EASY>>>OptionButton Select Error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stephen - 31 May 2007 19:12 GMT

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?

Reply to this Message

Susan - 31 May 2007 19:31 GMT

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

> Public Sub CompanySelect()
>
[quoted text clipped - 17 lines]
> 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

Reply to this Message

Stephen - 31 May 2007 19:49 GMT

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?

> stephen -
> try adding one more elseif..............
[quoted text clipped - 41 lines]
> :)
> susan

Reply to this Message

Tom Ogilvy - 31 May 2007 19:34 GMT

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

Signature

Regards,
Tom Ogilvy

> Hi Folks,
>
[quoted text clipped - 34 lines]
>
> Any ideas?

Reply to this Message

Stephen - 31 May 2007 19:50 GMT

Tom,

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

Thanks!

> Public Sub CompanySelect()
>
[quoted text clipped - 64 lines]
> >
> > Any ideas?

Reply to this Message

Tom Ogilvy - 31 May 2007 20:01 GMT

Yes, my typo.

Signature

Regards,
Tom Ogilvy

> Tom,
>
[quoted text clipped - 70 lines]
> > >
> > > Any ideas?

Reply to this Message

Stephen - 31 May 2007 20:11 GMT

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

??

> Yes, my typo.
>
[quoted text clipped - 72 lines]
> > > >
> > > > Any ideas?

Reply to this Message

Stephen - 31 May 2007 20:17 GMT

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!

> Yes, my typo.
>
[quoted text clipped - 72 lines]
> > > >
> > > > Any ideas?

Reply to this Message

Susan - 31 May 2007 20:47 GMT

:) 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

> Got it!!!
>
[quoted text clipped - 87 lines]
>
> - Show quoted text -

Reply to this Message

Stephen - 31 May 2007 21:36 GMT

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

Thanks Again!

> :) good!
> yes, tom usually has a more concise idea than me (re: the boolean
[quoted text clipped - 113 lines]
> >
> > - Show quoted text -

Reply to this Message