option buttons in a userform

[mortgageman]

If an OptionButtonx_Clicks() is an event handler and (to my way of thinking at least) should only trigger when the event happens (i.e. the user clicks that button), why does the macro require an "If optionbuttonx" in the very top of the code? Shouldn't the code only be triggered if the button was clicked in the first place?

Gene, "The Mortgage Man", Klein

 

[LwarenceD]

I need to let the user select one of three options,
EST, EDT or let them enter their own timezone offset.

Does this look right?
(Sorry this computer does not have Excel installed)


on a UserForm;

If OptionButton1 = True Then 
Range("O2") = -6    ' EST timezone offset
Else IF
If OptionButton2= True The
Range("O2") = -5    ' EDT timezone offset
Else IF
If OptionButton3= True The
Range("O2") = TextBox1.Text
End If 
{rest of code here}

If this is correct, how would I prevent the user from inputting data in the TextBox1 area?

Cheers
Lwrence

 

[HalfAce]

Hello LwarenceD,
The first part is close... Here's the proper syntax.

Private Sub CommandButton1_Click()
If OptionButton1 = True Then
  Range("O2") = -6    ' EST timezone offset
ElseIf OptionButton2 = True Then
  Range("O2") = -5    ' EDT timezone offset
ElseIf OptionButton3 = True Then
  Range("O2") = TextBox1.Text
End If
'{rest of code here}
End Sub

For the textbox question you might try something like this.

Private Sub TextBox1_Enter()
Me.OptionButton1 = False
Me.OptionButton2 = False
End Sub

Hope it helps.

 

[LwarenceD]

Thanks HalfAce,

I noted the error of THE instead of THEN after I posted. Glad you caught it too.. =)

Here is what I am trying o run now that I am at an Excel loaded computer.

If OptionButton1 = True Then
Sheets("CM").Select
Range("O12") = -6    ' EST timezone offset
End If
If OptionButton2 = True Then
Sheets("CM").Select
Range("O12") = -5    ' EDT timezone offset
End If
If OptionButton3 = True Then
Sheets("CM").Select
Range("O12") = TextBox13.Text
End If

I realized after I posted, it does not matter what the user inputs into TextBox13 as it will not transpose without the OptionButton3 activated.

Only problem I have now is the Range("O12") is not populating.

Any clues?

Cheers
Lwrence

 

[HalfAce]

Most welcome.
You'll want to refer to the userform if you haven't already in any code above these lines. Maybe try something like this.

Private Sub CommandButton1_Click()
With Me
  If .OptionButton1 = True Then
    Sheets("CM").Select
    Range("O12") = -6    ' EST timezone offset
  End If
  If .OptionButton2 = True Then
    Sheets("CM").Select
    Range("O12") = -5    ' EDT timezone offset
  End If
  If .OptionButton3 = True Then
    Sheets("CM").Select
    Range("O12") = TextBox13.Text
  End If
End With
End Sub

Hope it helps.

 

[LwarenceD]

Here's what i have so far, this is on a UserForm

Private Sub CommandButton1_Click()

If Not IsNumeric([TextBox1].Value) Or [TextBox1].Value < 1 Or [TextBox1].Value > 26 Then
        MsgBox "Number of CM Lines not defined or is less than 1 or greater than 26.", , _
               "Try Again":
               TextBox1.SetFocus
Exit Sub
End If

If Not IsNumeric([TextBox13].Value) And [OptionButton4].Value = False And [OptionButton2].Value = False Then
        MsgBox "TimeZone Offset not selected.", , _
               "Try Again":
               TextBox13.SetFocus
Exit Sub
End If


With Me
  If OptionButton4 = True Then
    TextBox13.Text = -6
  End If
  If OptionButton2 = True Then
    TextBox13.Text = -5
  End If
  If OptionButton5 = True Then
    TextBox13.Text = TextBox13.Text
  End If
End With



ActiveWorkbook.Sheets("CM").Activate

Range("O1:O11").ClearContents
Range("O13:P29").ClearContents
Range("O1") = TextBox1.Text
Range("O2") = TextBox2.Text
Range("O3") = TextBox3.Text
Range("O7") = TextBox4.Text
Range("O8") = TextBox5.Text
Range("O10") = TextBox6.Text
Range("O11") = TextBox7.Text
Range("O12") = TextBox13.Text
Range("O13") = TextBox11.Text

ActiveWorkbook.Sheets("Chill").Activate
Range("B1:B2").ClearContents
Range("B1") = TextBox1.Text
Range("B2") = TextBox2.Text

Unload UserForm1

Sheets("CM").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Main Menu").Select
    Range("C2").Select

End Sub

It appear to be working, but if I leave ANY of the TextBox's empty it Breaks on me.

How would I allow "Empty" inputs into the TextBox's if the user chooses not to enter data in TextBox2 thru TextBox11 and TextBox13?

Cheers
Lwrence

 

[HalfAce]

Good morning Lwrence,
I'm a little unclear on what you mean by "it Breaks on me."
Is it giving you an error? (If so, what line is highlighted when you hit Debug?)

I'm also not clear on what you meant here: "How would I allow "Empty" inputs into the TextBox's if the user chooses not to enter data in TextBox2 thru TextBox11 and TextBox13?"
The only textboxes that require an input are Tb's 1 & 13. (And your tests for numeric entries and values within the specified limits seem to be working great.)
It all seems to work fine for me, simply leaving the appropriate cells in the CM & Chill sheets blank if their textbox(es) were left blank. (Again, I'm not sure; is that what you were asking about?)

I've taken the liberty to do a little editing/rearranging in your code and have explained it as I went. I think you'll pretty easily see what I've done.

In the first two If statements, I didn't change anything, just rearranged it for easier reading.

In the third - fifth If statements, (for the optionbuttons) I just shortened them up a bit. (Whenever you have a "one liner" if statement like that, you don't need the End If statement.)

Then I edited (just a little) to show how you can make your sheet entries (and printing) without having to actually activate/select the sheets themselves. This makes your code (in general) run a little quicker and while it won't make any real difference in a routine of this size, is just a good programming practice to get into and I thought you might be interested.

Aside from all that, this (and your original code) seem to working as expected. What is it doing/not doing that you'd like to change?

[EDIT:]
I guess it would be good to include the code

Private Sub CommandButton1_Click()
If Not IsNumeric([TextBox1].Value) _
  Or [TextBox1].Value < 1 _
  Or [TextBox1].Value > 26 Then
    MsgBox "Number of CM Lines not defined or is less than 1 or greater than 26.", , _
    "Try Again"
    TextBox1.SetFocus
    Exit Sub
End If

If Not IsNumeric([TextBox13].Value) _
  And [OptionButton4].Value = False _
  And [OptionButton2].Value = False Then
    MsgBox "TimeZone Offset not selected.", , _
    "Try Again"
    TextBox13.SetFocus
    Exit Sub
End If

If OptionButton4 = True Then TextBox13.Text = -6

If OptionButton2 = True Then TextBox13.Text = -5
'*** Not sure of your intent here because _
     TextBox13.Text = TextBox13.Text whether _
     or not OptionButton5 = True ***
If OptionButton5 = True Then TextBox13.Text = TextBox13.Text

'*** There's no need to activate Sheets("CM")... ***
'ActiveWorkbook.Sheets("CM").Activate
With Sheets("CM")
    .Range("O1:O11").ClearContents
    .Range("O13:P29").ClearContents
    .Range("O1") = TextBox1.Text
    .Range("O2") = TextBox2.Text
    .Range("O3") = TextBox3.Text
    .Range("O7") = TextBox4.Text
    .Range("O8") = TextBox5.Text
    .Range("O10") = TextBox6.Text
    .Range("O11") = TextBox7.Text
    .Range("O12") = TextBox13.Text
    .Range("O13") = TextBox11.Text
End With

'*** No need to activate Sheets("Chill")... ***
'ActiveWorkbook.Sheets("Chill").Activate
With Sheets("Chill")
    .Range("B1:B2").ClearContents
    .Range("B1") = TextBox1.Text
    .Range("B2") = TextBox2.Text
End With

Unload UserForm1

'*** No need to select Sheets("CM") to print it. ***
'Sheets("CM").Select
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("CM").PrintOut Copies:=1, Collate:=True

'*** If your userform is being shown from the Main Menu sheet then _
     you won't need to select this sheet either because it's never been left. ***
Sheets("Main Menu").Select
Range("C2").Select
End Sub