I currently have a UserForm that involves dates and times which loads when a button is pressed. When it appears it comes with example default values already filled in. However I have decided that I want the UserForm to also load when a cell is clicked on. However when the cell is clicked on I want the UserForm to appear with dates and times already filled in (based on the Cell location)
So far I have the following: This is in the UserForm
Private Sub UserForm_Initialize()
'Set Duration Box default value to 1
DurationBox.Value = 1
'Set court one as default
OptionButton1.Value = True
'Set Default (example) Date
DateBox.Text = "Ex 22/05/2001"
'Set Default start time
StartBox.Text = "08:00"
'Set Default Number of weeks
WeeksBox.Text = 1
In the cells though I have the following:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CurDate As String
Dim CurStart As String
If Target.Row > 6 And Target.Row < 21 And Target.Column > 5 And
Target.Column < 13 Then
CurRow = ActiveCell.Row
CurCol = ActiveCell.Column
CurDate = Cells(5, CurCol).Text
CurStart = Cells(CurRow, 1).Text
UserForm1.Show
End If
End Sub
When the UserForm loads in this in this way I want CurDate and CurStart to repalce the default DateBox and StartBox
However if I simply put:
'Set Default (example) Date
DateBox.Text = CurDate
'Set Default start time
StartBox.Text = CurStart
after loading the UserForm Firstly it doesnt do anything until after I have closed the UserForm and secondly it then throws up an error.
Essentialy I want to give the UserForm a different Initialization based on how it is activated. I could just have two UserForms that do exactly the same thing however the rest of the code is very long and its still liable to be changed.
You have two options:
1. Move all (or most) of your code in UserForm_Initialize to UserForm_Activate. Then it will happen right after your .Show call.
2. Set the values from your calling code instead of in the form:
UserForm1.DateBox.Text = CurDate
UserForm1.Show