EASY>>>OptionButton Select Error

[Guest]

Hi Folks,

I'm trying to save my users from themselves by forcing them down a path with
choices. Specifically I've created a form that asks for a selection. The
selections will take them to the appropriate sheet to the next step. All my
sheets are hidden until necessary and so far it's nice and clean.

Everything works great except if there is no selection made from the initial
choices. If no choice is made and the user click the OK button, the form
closes and they are stuck. See code below...

Public Sub CompanySelect()

Dim frm As New frmCompanySelect
'Dim s As String

frm.Show

If frm.btnCORP.Value = True Then
Sheets("Query CORP").Visible = True
Sheets("Query CORP").Select
Range("D6").Select

ElseIf frm.btnNJ.Value = True Then
Sheets("Query NJ").Visible = True
Sheets("Query NJ").Select
Range("D6").Select

ElseIf frm.btnVA.Value = True Then
Sheets("Query VA").Visible = True
Sheets("Query VA").Select
Range("D6").Select
End If

Also, I'd like to put a "Back To Choices" button on each sheet that will
invoke the form again and a simple... "Call companySelect- is not doing it.

Any ideas?

 

[Susan]

stephen -
try adding one more elseif..............

Public Sub CompanySelect()

Dim frm As New frmCompanySelect
'Dim s As String

frm.Show

If frm.btnCORP.Value = True Then
Sheets("Query CORP").Visible = True
Sheets("Query CORP").Select
Range("D6").Select

ElseIf frm.btnNJ.Value = True Then
Sheets("Query NJ").Visible = True
Sheets("Query NJ").Select
Range("D6").Select

ElseIf frm.btnVA.Value = True Then
Sheets("Query VA").Visible = True
Sheets("Query VA").Select
Range("D6").Select

elseif frm.btnCORP.value = false and _
frm.btnNJ.value = false and _
frm.btnVA.value = false then
msgbox "One option must be chosen!"
exit sub

End If

Also, I'd like to put a "Back To Choices" button on each sheet that will
invoke the form again and a simple... "Call companySelect- is not doing it.

re: back to choices button, just have button re-load the userform; in
the userform initialization (i assume) all your option buttons are set
to false, so they'll be able to start again.

re-reading your post i don't think i've understood you quite
correctly, but the ideas should still help you out, even if i've got
them in the wrong places.

susan

 

[Guest]

Public Sub CompanySelect()

Dim frm As New frmCompanySelect
'Dim s As String
Dim bSuccess as Boolean
bSuccess = False
do
frm.Show

If frm.btnCORP.Value = True Then
Sheets("Query CORP").Visible = True
Sheets("Query CORP").Select
Range("D6").Select
bSuccess
ElseIf frm.btnNJ.Value = True Then
Sheets("Query NJ").Visible = True
Sheets("Query NJ").Select
Range("D6").Select
bSuccess

ElseIf frm.btnVA.Value = True Then
Sheets("Query VA").Visible = True
Sheets("Query VA").Select
Range("D6").Select
bSuccess

End If

Loop while not bSuccess

 

[Guest]

Susan,

Tom's suggestion of adding a simple boolean worked with some slight
modification.

but I'm not following you on initializing the uer form?

 

[Guest]

Tom,

On the money although I had to add = True under each If for the bSuccess.

Thanks!

 

[Guest]

Yes, my typo.

--
Regards,
Tom Ogilvy

Tom,

On the money although I had to add = True under each If for the bSuccess.

Thanks!

 

[Guest]

How about my other sticking point...

I have a Back To Choices button that is not initializing the form...

Sub BackToChoices()

Call CompanySelect

End Sub

the form is...

Public Sub CompanySelect()

Dim frm As New frmCompanySelect
Dim bSuccess As Boolean
bSuccess = False

Do
frm.Show

If frm.btnCORP.Value = True Then
Sheets("Query FDR_CORP").Visible = True
Sheets("Select").Visible = False
Sheets("Query FDR_CORP").Select
Range("D6").Select
bSuccess = True

ElseIf frm.btnNJ.Value = True Then
Sheets("Query FDR_NJ").Visible = True
Sheets("Select").Visible = False
Sheets("Query FDR_NJ").Select
Range("D6").Select
bSuccess = True

ElseIf frm.btnVA.Value = True Then
Sheets("Query FDR_VA").Visible = True
Sheets("Select").Visible = False
Sheets("Query FDR_VA").Select
Range("D6").Select
bSuccess = True

End If

Loop While Not bSuccess

End Sub


??

 

[Guest]

Got it!!!

I have my sub in my workbook instead of a seperate module. I created a new
mod and dumped my form code in there and wala!

 

[Susan]

good!
yes, tom usually has a more concise idea than me (re: the boolean
values).

usually when you have a userform, in the userform code you have

sub userform1_initialize()
end sub

that's where, when it's loading the form, you put everything blank &
values to false, the way you want it when the user first sees it (all
the checkboxes blank, all the option buttons false, all the textboxes
blank, etc.). to reset the form to be used again, you can just re-
call the userform1_initialize sub, which sets everything back to false/
clear etc.

but also, you're just doing Frm.Show
usually you'd do
Load Frm
Frm.Show

the "loading" part is where the initialization would come in.
oh well, i'm glad you got it working!

susan

 

[Guest]

i am nothing without the guidance of the masters...

Thanks Again!