Tech Support Guy banner
Status
Not open for further replies.
1 - 14 of 14 Posts

·
Registered
Joined
·
137 Posts
Discussion Starter · #1 ·
Hi all,

I got a question,
I made an userform in VBA (excel) which I want users to fill in after they press on a command button.

This part I succeeded in.

Now is the next step..
All the data has to be filled in and should display at a blank line in the sheet after they press "ok".

Now I really don't have a clue how I should do this.. I know I should code something, but I don't know what.

Could anyone please help me with this?

I've added the sheet in the attachment.
In communicationmaterials you should have the possibility to select one of the items listed on the sheet, same for the Person (also a couple of names from which you are able to select one from). The rest should be typed in manually.

Thanks in advance!
 

Attachments

·
Registered
Joined
·
2,556 Posts
here this will get get you going in the right direction,

Code:
Option Explicit

Private Sub Cancel_Click()

UserForm1.Hide

End Sub
Code:
Private Sub Ok_Click()


Dim ws As Worksheet
Dim ctl As Control

Set ws = ActiveSheet

' finds the last row in column 12 then adds 1 to reference the empty line
    lrow = (ws.Cells(ws.Rows.Count, 12).End(xlUp).Row) + 1

' cells(ROW,COLUMN)
' I renamed the controls to make it meaningful
' you can add the rest, just type it out when you get to me.
' it will pop up the list where you can pick the control names
        Cells(lrow, 12) = Me.cobComMat
        Cells(lrow, 13) = Me.cobNumber
        Cells(lrow, 14) = Me.cobPerson
        Cells(lrow, 15) = Me.txtBorrowedTo

            ' this loops through the controls on the form and
            ' if they are Combos or textboxes it will clear them
        For Each ctl In UserForm1.Controls
        
                If TypeName(ctl) = "ComboBox" Or TypeName(ctl) = "TextBox" Then
                
                    ctl = ""
                
                End If
        
             
        Next
        
        UserForm1.Hide


End Sub
 

·
Registered
Joined
·
2,556 Posts
here is the Excel file with the code I added, one thing I didn't do was correct the Tab Order, look at the properties of each control and assign them a number for the order you want to tab through them.

I commented the insert line on the button click as you don't need it

Private Sub CommandButton1_Click()
Load UserForm1
UserForm1.Show
'Rows(ActiveCell.Row).insert
End Sub
 

Attachments

·
Registered
Joined
·
137 Posts
Discussion Starter · #4 ·
Thanks for so far, now this works.
I actually don't understand one thing,
Code:
' this loops through the controls on the form and
            ' if they are Combos or textboxes it will clear them
        For Each ctl In UserForm1.Controls
        
                If TypeName(ctl) = "ComboBox" Or TypeName(ctl) = "TextBox" Then
                
                    ctl = ""
What does this piece of code do?

And one other question, how do I add the choises to the selector-menu's? So that people can select a person from a list in the userform. <-- This piece I fixed.

But I got one other question, people need to insert a date, how can I be sure that a correct date is inserted (31/12/2009 for example instead 12/31/09)
 

·
Registered
Joined
·
137 Posts
Discussion Starter · #5 ·
To make my question more clarifying..

People need to insert a date in a textbox. Now I want a validation that a correct date is typed in. This means that people MUST insert the date as 20-01-2009 and not like 20-01-09, 20-1-09 etc (these give a msgbox with " Invalid Date" )

How can I validate this? I searched google etc for answers but I couldn't find any correct ones (only automatic insert date today etc)

I tried these for example:
Code:
Private Sub txtDatePlan_Change()
txtDatePlan.Value = Format(txtDatePlan.Value, "dd/mm/yyyy")
End Sub

OR

Private Sub txtDatePlan_Change()
txtDatePlan.Text = Format(txtDatePlan.Text, "dd/mm/yyyy")
End Sub
When people start inserting a date with these codes, they start for example with typing a 1, then the date 31-12-1899 shows up in the textbox, typing a 2 gives 01-01-1900, typing a 3 gives 02-01-1900 etc. So people can't insert a date themselves.
 

·
Registered
Joined
·
2,556 Posts
sorry dud...busy....try this...

Code:
   Private Sub txtDateBor_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

If IsDate(Me.txtDateBor) Then

'edit
Me.txtDateBor = Format(Me.txtDateBor, "dd/mm/yyyy")

Else

MsgBox "Please enter date in correct format dd/mm/yyyy"

Me.txtDateBor.SetFocus
Me.txtDateBor = ""

' colors the field
Me.txtDateBor.BackColor = RGB(230, 255, 50)

End If
Add this to Uncolor the box after the correct date is filled in...I found that even with setfocus the cursor didn't appear so this looks better with coloring

Code:
Private Sub txtDateBor_AfterUpdate()

If Me.txtDateBor <> "" Then

Me.txtDateBor.BackColor = RGB(255, 255, 255)

End If

End Sub
 

·
Registered
Joined
·
137 Posts
Discussion Starter · #7 ·
I solved it with the following code:

Code:
If Len(txtDateBor.Value) <> 10 Then
        MsgBox "typ dd-mm-yyyy in 'Date Borrowed'! "
        Exit Sub
    End If


' terug verwacht date


If Len(txtDatePlan.Value) <> 10 Then
        MsgBox "typ dd-mm-yyyy in 'Datum expected back'! "
        Exit Sub
    End If
    ' eind terug verwacht date
This way it only allows to insert exactly 10 'numbers' and shows that you have to fill in dd-mm-yyyy (each combination is possible, but the user knows he has to fill the date in this way.

The only new thing now, is the following. When the date expected back is today, a message should be send to an e-mailadres. I found some ways to do this, but I get a problem. The date which is filled in, is a value but I can't compare it to anything (if I place =today() in a cell, I can't compare it to the filled in date (=if(today()=date;date;"") (for example =if(a1=c5;c5;"")

So does someone know how I can send an email when the date expected back is the same as today?
 

·
Registered
Joined
·
2,556 Posts
this should work, but I can't get the comparison to work to trigger the send. Also you should use my previous example for entering the date as checking for the Len 0f 10 won't stop letters or in valid number combinations.

* this can also be used to send another workbook other than active, I don't know how to just send a email alone though.

Code:
                     If Me.txtDateBor.Value = Date Then
                        
                    ActiveWorkbook.SendMail ("youremail")

                    End If
 

·
Registered
Joined
·
137 Posts
Discussion Starter · #9 ·
I found the solution.

Code:
Dim objExcel 
Dim objOutlook 
Dim objMail 
Dim objWB 
Dim objWS 
Dim vCell

Set objExcel = CreateObject("Excel.Application")
Set objOutlook = CreateObject("Outlook.Application")

objExcel.DisplayAlerts = False
objExcel.Workbooks.Open ("C:\filedestination.xls")
Set objWB = objExcel.Activeworkbook
Set objWS = objWB.ActiveSheet
For Each vCell In objWS.Range("E6:E" & objWS.Cells(objWS.Rows.Count, "E").End(-4162).Row).Cells

If FormatDateTime(vCell) <= FormatDateTime(Date) Then
If vCell.Offset(0, 5).Value <> "YES" Then
Set objMail = objOutlook.CreateItem(olMailItem)

objMail.To = "[email protected]"
objMail.Subject = "Communicatiemateriaal Email Alert"

objMail.Body = "Materiaal - " & vCell.offset(0, -4).Value & vbCrLf & _
"Aantal - " & vCell.offset(0, -3).Value & vbCrLf & _
"Vertegenwoordiger - " & vCell.offset(0, +2).Value & vbCrLf & _
"Uitgeleend aan - " & vCell.offset(0, -2).Value & vbCrLf & _
"Datum verwachte teruggave - " & vCell.offset(0, 0).Value

objMail.Send
vCell.Offset(0, 5).Value = "YES"
End If
End If
Next


objWB.Save
objWB.Close
objExcel.Quit
objOutlook.Quit
Set objExcel = Nothing
Set objWB = Nothing
Set objWS = Nothing
Set objMail = Nothing
Set objOutlook = Nothing
This is a .txt which I save as .vsb. If I 'open' this script, sends automatically an email to me when the date matches. Also it adds an 'Yes' 5 cells behind it, so If I (accidentally) run the script another time at the same day, I don't get the same e-mails sended.

I found this script on the internet and it works!
I also could use Windows Task Shedular? so the script runs automatically. But this I don't know yet and I have to look how this is done (don't know what Windows Task Shedular is, so I think I'm retarded?) LOL
 

·
Registered
Joined
·
137 Posts
Discussion Starter · #11 ·
Got Vista ;) LOL

But I will check this out this monday when I'm at work again.

Thanks for your help so far! I'm glad I got this far (I expected to never get this far).
 

·
Registered
Joined
·
137 Posts
Discussion Starter · #12 ·
I got one question left.

In the two Textboxes the dates are entered, which, after clicking OK, are shown in the sheet. Now I want the user to fill in manually a date also in the sheet (next row). Now I want this date to compare with one of the date which is filled in with the textbox.

So for example
D4 contains 09-09-2009 (added with the above developed macro)

E4 contains 10-09-2009 (manually filled in directly on the sheet)

Now I want to use this formula:
if(E4>D4;true;false)
but this doesn't work (it's always smaller for some reason)

How can I fix this?
both cells have the same Date cellproperties.(dd-mm-yyyy) but I guess one of the two is a value and the other text (I guess D4 is text).

I don't know how to fix this. Thanks in advance!
 

·
Registered
Joined
·
137 Posts
Discussion Starter · #14 ·
I haven't found a solution anymore. Bit anoying, while if this worked it would give a quick overview for which things are borrowed and which are allready back.

The same was I tried to make a Userform2 in which also a date could be entered. First a name should be selected in the first combobox and the second combobox should search the list to everything where this name is in front of and show the information behind it.

So for example,

Freddy inserted a row borrowing some Flags to Peter.
When Freddy got the flags back, he opens Userform2 and selects his own name. Now the second combobox searches the whole column to this name (freddy) and shows the column behind it where Peter is shown (so in the second combobox all persons are shown to which Freddy has borrowed communicationmaterials). After this Freddy fills in a textbox with the date the flags returned and after pressing 'OK' this date is filled in a column behind it.

To build the userform succeeded, the first Combobox also (these are a few standard names) but the second combobox and the Textbox didn't succeed.
 
1 - 14 of 14 Posts
Status
Not open for further replies.
Top