PDA

View Full Version : Problem initializing userform



bergjes
12-20-2012, 12:38 AM
Hi,

I'm building a userform with vba code I found here on this forum.
The form is almost finished, but here is my problem.

I want to initialize the controls when clicking on my calendar, but don't know how to accomplish this.

I probably have to use the code found in "ClearControls", but I don't know where to call the code from.
I succesfully used the code to initialize the form when changing the month or year, but really don't know how to do this for clicking a date within the same month.

Hope somebode can help me.

My form is attached.

Thanks,
Erik

I posted my question also on worksheet.nl but have had no reaction so far.

patel
12-20-2012, 01:57 AM
It's not clear for me what does not run

Bob Phillips
12-20-2012, 02:00 AM
It should go into Userform_Activate. As it happens, it is already called there, but it is only invoked if there is an error, or if that Evaluate returns a value > 0 (But as it looks like pure nonsense code, I can't believe it ever does anything other than error).

bergjes
12-20-2012, 02:39 AM
I'll try to explain.

If you set the calendar to 2 januari - 2013 and the winkel to winkel3 the form displays the record of that particular day and winkel. When I click on the calendar, say 3 januari 2013, the still displays the previous called record. What I want is that when I click the calendar on a different date the form should clear the form components.
Hopes this makes ist clear? :)

bergjes
12-20-2012, 02:43 AM
I don't quit understand what you mean?
My knowledge of VBA is minimum, so what do you suggest to make it work the way I want?

Bob Phillips
12-20-2012, 09:30 AM
You need to work out what that evaluate should do or get rid of it. We can't tell you what you want.

patel
12-20-2012, 10:21 AM
change this sub
Private Sub DateButton_Click()
Dim Rw As Long
Dim sModel As String
Dim lDatum As Long
Dim D As Date
D = DateSerial(UserForm3.SpinYear.Value, UserForm3.SpinMonth.Value, CInt(DateButton.Caption))
UserForm3.LDateOut.Caption = Format(D, "dd-mm-yyyy")
UserForm3.LDateOut.ForeColor = DateButton.ForeColor
UserForm3.TextBox1.Value = ""
UserForm3.TextBox2.Value = ""
UserForm3.TextBox3.Value = ""
On Error GoTo ErrHandler:

sModel = UserForm3.ComboBox1.Value
If sModel = vbNullString Then Exit Sub
lDatum = CDate(UserForm3.LDateOut.Caption)
Rw = Evaluate("MATCH(""" & lDatum & UserForm3.ComboBox1.Value & """,data_datum&data_winkel,0)+1")
If Rw > 0 Then


UserForm3.TextBox1.Value = Format(Sheets("DataInput").Cells(Rw, 3).Value)
UserForm3.TextBox2.Value = Format(Sheets("DataInput").Cells(Rw, 4).Value)
UserForm3.TextBox3.Value = Format(Sheets("DataInput").Cells(Rw, 5).Value)

Else

ErrHandler:

UserForm3.TextBox1.Value = ""
UserForm3.TextBox2.Value = ""
UserForm3.TextBox3.Value = ""

End If


End Sub