I am new to UserForms (Excel 2003). I created a UserForm with 3 options that
input data into Cell A3 in a worksheet. However, you can exit the Userform
by clicking 'OK' without selecting any of the options. I need at least one
Option to be selected. I thought the following Code would help, but doesn't.
I am hoping someone can help.

Private Sub OK_Click()

If Range("A3") = " " Then
MsgBox "You Must Select the Source from this menu"
UserForm1.SetFocus

Exit Sub
End If

Unload Me

End Sub


Thanks
Linda

Linda,

You are not testing if A3 is empty, but if it has a single space
character.

Try this (no space between the quotation signs):

If Range("A3") ="" then

Regards,
Per

On 6 Apr., 20:18, mathel wrote:
I am new to UserForms (Excel 2003). *I created a UserForm with 3 options that
input data into Cell A3 in a worksheet. *However, you can exit the Userform
by clicking 'OK' without selecting any of the options. *I need at least one
Option to be selected. *I thought the following Code would help, but doesn't.
*I am hoping someone can help.

Private Sub OK_Click()

If Range("A3") = " " Then
MsgBox "You Must Select the Source from this menu"
UserForm1.SetFocus

Exit Sub
* * End If

Unload Me

End Sub

Thanks
Linda

I'm not sure what you're doing, but if your form has two commandbuttons on it
(cancel and ok), then you may not want to apply the value to A3 until the Ok
button is clicked.

If you tie the textbox/optionbutton/checkbox/whatever (via the .controlsource
property) back to that cell, you may find that the user makes a change on the
userform, then decides that they made a mistake and want to cancel, it would be
too late. The value has already been copied to the cell.

You could check all the requirements within the userform's ok_click event. Then
decide how to update A3.

If that seems reasonable and you want to try it and have trouble, you may want
to explain what 3 options (optionbuttons???) you're using on the userform. And
what should be placed in A3 (and what sheet?).

mathel wrote:

I am new to UserForms (Excel 2003). I created a UserForm with 3 options that
input data into Cell A3 in a worksheet. However, you can exit the Userform
by clicking 'OK' without selecting any of the options. I need at least one
Option to be selected. I thought the following Code would help, but doesn't.
I am hoping someone can help.

Private Sub OK_Click()

If Range("A3") = " " Then
MsgBox "You Must Select the Source from this menu"
UserForm1.SetFocus

Exit Sub
End If

Unload Me

End Sub

Thanks
Linda

--

Dave Peterson

Thank you. Removing the space ( If Range("A3") ="" then) has helped and will
invoke the MsgBox. Now I am getting an error code on "UserForm1.SetFocus".
Reading through other Posts I saw Option Buttons should be grouped. I
grouped them and under the Group Properties name them 'Source'. I then
changed the last line in the code to read: 'Source.SetFocus' .......

This is where I get Error 424. I hope you can help, I have no idea what I
am doing at this point!
Thanks
--
Linda


"Per Jessen" wrote:

Linda,

You are not testing if A3 is empty, but if it has a single space
character.

Try this (no space between the quotation signs):

If Range("A3") ="" then

Regards,
Per

On 6 Apr., 20:18, mathel wrote:
I am new to UserForms (Excel 2003). I created a UserForm with 3 options that
input data into Cell A3 in a worksheet. However, you can exit the Userform
by clicking 'OK' without selecting any of the options. I need at least one
Option to be selected. I thought the following Code would help, but doesn't.
I am hoping someone can help.

Private Sub OK_Click()

If Range("A3") = " " Then
MsgBox "You Must Select the Source from this menu"
UserForm1.SetFocus

Exit Sub
End If

Unload Me

End Sub

Thanks
Linda

I was hoping to keep this short, but.... I have a worksheet that I need to
ensure the User fills in cell A3. Cell A3 must be one of 3 options, 'ATM',
'Operations' or 'Errors & Omissions'.

I set the Code on the worbook so that on opening, the UserForm immediately
opens with the 3 Options and 'OK' button at the bottom. Each of the Options
are coded:
Private Sub ATM_Click()

Range("A3").Select
ActiveCell.FormulaR1C1 = "ATM"

End Sub
......etc

I need to make sure one of them is selected and Cell A3 is not left blank.
Maybe I'm looking at this the wrong way. I tried using a 'List' to validate
but it still does not prevent a blank cell.

I hope this helps to explain better.

--
Linda


"Dave Peterson" wrote:

I'm not sure what you're doing, but if your form has two commandbuttons on it
(cancel and ok), then you may not want to apply the value to A3 until the Ok
button is clicked.

If you tie the textbox/optionbutton/checkbox/whatever (via the .controlsource
property) back to that cell, you may find that the user makes a change on the
userform, then decides that they made a mistake and want to cancel, it would be
too late. The value has already been copied to the cell.

You could check all the requirements within the userform's ok_click event. Then
decide how to update A3.

If that seems reasonable and you want to try it and have trouble, you may want
to explain what 3 options (optionbuttons???) you're using on the userform. And
what should be placed in A3 (and what sheet?).

mathel wrote:

I am new to UserForms (Excel 2003). I created a UserForm with 3 options that
input data into Cell A3 in a worksheet. However, you can exit the Userform
by clicking 'OK' without selecting any of the options. I need at least one
Option to be selected. I thought the following Code would help, but doesn't.
I am hoping someone can help.

Private Sub OK_Click()

If Range("A3") = " " Then
MsgBox "You Must Select the Source from this menu"
UserForm1.SetFocus

Exit Sub
End If

Unload Me

End Sub

Thanks
Linda

--

Dave Peterson

Here's a way to do it (rename option buttons as required)

Private OK_Click()
If Me.OptionButton1.Value = True Then Range("A3").Value = "ATM"
If Me.OptionButton2.Value = True Then Range("A3").Value = "Operations"
If Me.OptionButton3.Value = True Then Range("A3").Value = "Errors &
Omissions"

If Range("A3").Value = "" Then
Msg = MsgBox("You Must Select the Source from this menu", _
vbInformation, "Regards, Per Jessen")
Else
Unload Me
End If
End Sub

Regards,
Per


On 6 Apr., 22:06, mathel wrote:
I was hoping to keep this short, but.... I have a worksheet that I need to
ensure the User fills in cell A3. *Cell A3 must be one of 3 options, 'ATM',
'Operations' or 'Errors & Omissions'. *

I set the Code on the worbook so that on opening, the UserForm immediately
opens with the 3 Options and 'OK' button at the bottom. *Each of the Options
are coded:
Private Sub ATM_Click()

Range("A3").Select
ActiveCell.FormulaR1C1 = "ATM"

End Sub
.....etc

I need to make sure one of them is selected and Cell A3 is not left blank.. *
Maybe I'm looking at this the wrong way. *I tried using a 'List' to validate
but it still does not prevent a blank cell.

I hope this helps to explain better.

--
Linda



"Dave Peterson" wrote:
I'm not sure what you're doing, but if your form has two commandbuttons on it
(cancel and ok), then you may not want to apply the value to A3 until the Ok
button is clicked.

If you tie the textbox/optionbutton/checkbox/whatever (via the .controlsource
property) back to that cell, you may find that the user makes a change on the
userform, then decides that they made a mistake and want to cancel, it would be
too late. *The value has already been copied to the cell.

You could check all the requirements within the userform's ok_click event. *Then
decide how to update A3.

If that seems reasonable and you want to try it and have trouble, you may want
to explain what 3 options (optionbuttons???) you're using on the userform. *And
what should be placed in A3 (and what sheet?).

mathel wrote:

I am new to UserForms (Excel 2003). *I created a UserForm with 3 options that
input data into Cell A3 in a worksheet. *However, you can exit the Userform
by clicking 'OK' without selecting any of the options. *I need at least one
Option to be selected. *I thought the following Code would help, but doesn't.
*I am hoping someone can help.

Private Sub OK_Click()

If Range("A3") = " " Then
MsgBox "You Must Select the Source from this menu"
UserForm1.SetFocus

Exit Sub
* * End If

Unload Me

End Sub

Thanks
Linda

--

Dave Peterson- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

This works perfectly!

Thank you!
--
Linda


"Per Jessen" wrote:

Here's a way to do it (rename option buttons as required)

Private OK_Click()
If Me.OptionButton1.Value = True Then Range("A3").Value = "ATM"
If Me.OptionButton2.Value = True Then Range("A3").Value = "Operations"
If Me.OptionButton3.Value = True Then Range("A3").Value = "Errors &
Omissions"

If Range("A3").Value = "" Then
Msg = MsgBox("You Must Select the Source from this menu", _
vbInformation, "Regards, Per Jessen")
Else
Unload Me
End If
End Sub

Regards,
Per


On 6 Apr., 22:06, mathel wrote:
I was hoping to keep this short, but.... I have a worksheet that I need to
ensure the User fills in cell A3. Cell A3 must be one of 3 options, 'ATM',
'Operations' or 'Errors & Omissions'.

I set the Code on the worbook so that on opening, the UserForm immediately
opens with the 3 Options and 'OK' button at the bottom. Each of the Options
are coded:
Private Sub ATM_Click()

Range("A3").Select
ActiveCell.FormulaR1C1 = "ATM"

End Sub
.....etc

I need to make sure one of them is selected and Cell A3 is not left blank..
Maybe I'm looking at this the wrong way. I tried using a 'List' to validate
but it still does not prevent a blank cell.

I hope this helps to explain better.

--
Linda



"Dave Peterson" wrote:
I'm not sure what you're doing, but if your form has two commandbuttons on it
(cancel and ok), then you may not want to apply the value to A3 until the Ok
button is clicked.

If you tie the textbox/optionbutton/checkbox/whatever (via the .controlsource
property) back to that cell, you may find that the user makes a change on the
userform, then decides that they made a mistake and want to cancel, it would be
too late. The value has already been copied to the cell.

You could check all the requirements within the userform's ok_click event. Then
decide how to update A3.

If that seems reasonable and you want to try it and have trouble, you may want
to explain what 3 options (optionbuttons???) you're using on the userform. And
what should be placed in A3 (and what sheet?).

mathel wrote:

I am new to UserForms (Excel 2003). I created a UserForm with 3 options that
input data into Cell A3 in a worksheet. However, you can exit the Userform
by clicking 'OK' without selecting any of the options. I need at least one
Option to be selected. I thought the following Code would help, but doesn't.
I am hoping someone can help.

Private Sub OK_Click()

If Range("A3") = " " Then
MsgBox "You Must Select the Source from this menu"
UserForm1.SetFocus

Exit Sub
End If

Unload Me

End Sub

Thanks
Linda

--

Dave Peterson- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -