| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
EASYOptionButton Select Error
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EASYOptionButton Select Error
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. 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EASYOptionButton Select Error
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? "Susan" wrote: 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. 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EASYOptionButton Select Error
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 -- Regards, Tom Ogilvy "Stephen" wrote: 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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EASYOptionButton Select Error
Tom,
On the money although I had to add = True under each If for the bSuccess. Thanks! "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Stephen" wrote: 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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EASYOptionButton Select Error
Yes, my typo.
-- Regards, Tom Ogilvy "Stephen" wrote: Tom, On the money although I had to add = True under each If for the bSuccess. Thanks! "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Stephen" wrote: 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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EASYOptionButton Select Error
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 ?? "Tom Ogilvy" wrote: Yes, my typo. -- Regards, Tom Ogilvy "Stephen" wrote: Tom, On the money although I had to add = True under each If for the bSuccess. Thanks! "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Stephen" wrote: 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? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EASYOptionButton Select Error
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! "Tom Ogilvy" wrote: Yes, my typo. -- Regards, Tom Ogilvy "Stephen" wrote: Tom, On the money although I had to add = True under each If for the bSuccess. Thanks! "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Stephen" wrote: 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? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EASYOptionButton Select Error
:) good!
yes, tom usually has a more concise idea than me ( 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 On May 31, 3:17 pm, Stephen wrote: 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! "Tom Ogilvy" wrote: Yes, my typo. -- Regards, Tom Ogilvy "Stephen" wrote: Tom, On the money although I had to add = True under each If for the bSuccess. Thanks! "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Stephen" wrote: 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?- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Use optionbutton value to select code to run. | Excel Programming | |||
| Range.Select error and screen maximizing error in Workbook_Open() | Excel Programming | |||
| Range.Select error and screen maximizing error in Workbook_Open() | Excel Programming | |||
| easy one - select a cell | Excel Programming | |||
| SendKeys command to select an OptionButton | Excel Programming | |||
Hybrid Mode
