PDA

View Full Version : [SOLVED] Option Buttons in UserForm



vzachin
03-08-2010, 09:51 AM
hi,

this is my first time delving into userforms.
i have 3 option buttons marked Today, Yesterday, Specific Date.

If the Today button is clicked, then it would be Today's Date.
If the Yesterday button is clicked, i need to know the previous workday.
If the Specific Date is clicked, i have a text field for inputting a date. how do i enforce that an actual date is entered?



thanks
zach

mbarron
03-08-2010, 10:30 AM
The following goes with the Click event for the button. The caveat is that if the previous Friday is a holiday, the macro does not take that into account.


Private Sub CommandButton1_Click()
Dim sDate As Date, dCheck As Date
sDate = Date
If OptionButton1 = True Then
TextBox1 = sDate
ElseIf OptionButton2 = True Then
If Weekday(sDate, vbMonday) = 1 Then
TextBox1 = sDate - 2
Else
TextBox1 = sDate - 1
End If
ElseIf OptionButton3 = True Then
On Error Resume Next
redodate:
dCheck = Application.InputBox("Date Please")
If Err.Number = 13 Then
MsgBox "Invalid Date, please try again"
Err.Clear
GoTo redodate
Else
TextBox1 = dCheck
End If
End If
End Sub

Bob Phillips
03-08-2010, 10:36 AM
Sub test()
Const DATE_FORMAT As String = "mm/dd/yy"
With ActiveSheet
If .OLEObjects("OptionButton1").Object.Value Then
.TextBox1.Text = Format(Date, DATE_FORMAT)
ElseIf .OLEObjects("OptionButton2").Object.Value Then
.TextBox1.Text = Format(Date - 1, DATE_FORMAT)
End If
End With
End Sub

vzachin
03-08-2010, 11:18 AM
hi MBarron,

Thanks for the coding. works great.

Bob,
i couldn't get your code to work. received an error message "Unable to Get the OLEObject property of the Worksheet Class"


thanks again
zach