| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combobox linked to Custom Views
Hello, I have trouble with linking combobox selection to the custom views
I've created. Please help! In advance, thank you. I created an ActiveX control combobox and have teh following code: Dim bgCmbox As ComboBox Private Sub ComboBox1_Change() 'With Worksheets(1) Dim bgCmbox As ComboBox bgCmbox.AddItem " ", 1 bgCmbox.AddItem "(All)", 2 bgCmbox.AddItem "A", 3 bgCmbox.AddItem "B", 4 bgCmbox.AddItem "C", 5 bgCmbox.AddItem "D", 6 bgCmbox.AddItem "E", 7 bgCmbox.AddItem "F", 8 If bgCmbox.Value = "(All)" Then ActiveWorkbook.CustomViews("All").Show ElseIf bgCmbox.Value = "A" Then ActiveWorkbook.CustomViews("A").Show ElseIf bgCmbox.Value = "B" Then ActiveWorkbook.CustomViews("B").Show ElseIf bgCmbox.Value = "C" Then ActiveWorkbook.CustomViews("C").Show ElseIf bgCmbox.Value = "D" Then ActiveWorkbook.CustomViews("D").Show ElseIf bgCmbox.Value = "E" Then ActiveWorkbook.CustomViews("E").Show ElseIf bgCmbox.Value = "F" Then ActiveWorkbook.CustomViews("F").Show End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
I would add the options to the combobox a single time--maybe in the
workbook_open event? Option Explicit Private Sub Workbook_Open() With Worksheets("Sheet1").bgCmbox .Clear 'existing options .AddItem "(All)" .AddItem "A" .AddItem "B" .AddItem "C" .AddItem "D" .AddItem "E" .AddItem "F" End With End Sub Then since you're using the same options as the custom view names, you can use something like this in the combobox change event: Option Explicit Private Sub bgCmbox_Change() On Error Resume Next Me.Parent.CustomViews(bgCmbox.Value).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub MK wrote: Hello, I have trouble with linking combobox selection to the custom views I've created. Please help! In advance, thank you. I created an ActiveX control combobox and have teh following code: Dim bgCmbox As ComboBox Private Sub ComboBox1_Change() 'With Worksheets(1) Dim bgCmbox As ComboBox bgCmbox.AddItem " ", 1 bgCmbox.AddItem "(All)", 2 bgCmbox.AddItem "A", 3 bgCmbox.AddItem "B", 4 bgCmbox.AddItem "C", 5 bgCmbox.AddItem "D", 6 bgCmbox.AddItem "E", 7 bgCmbox.AddItem "F", 8 If bgCmbox.Value = "(All)" Then ActiveWorkbook.CustomViews("All").Show ElseIf bgCmbox.Value = "A" Then ActiveWorkbook.CustomViews("A").Show ElseIf bgCmbox.Value = "B" Then ActiveWorkbook.CustomViews("B").Show ElseIf bgCmbox.Value = "C" Then ActiveWorkbook.CustomViews("C").Show ElseIf bgCmbox.Value = "D" Then ActiveWorkbook.CustomViews("D").Show ElseIf bgCmbox.Value = "E" Then ActiveWorkbook.CustomViews("E").Show ElseIf bgCmbox.Value = "F" Then ActiveWorkbook.CustomViews("F").Show End If End Sub -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
Thank you for the help but I still get a run-time error 91, saying that
object variable or with block is not set. Can you advise? Thank you. "Dave Peterson" wrote: I would add the options to the combobox a single time--maybe in the workbook_open event? Option Explicit Private Sub Workbook_Open() With Worksheets("Sheet1").bgCmbox .Clear 'existing options .AddItem "(All)" .AddItem "A" .AddItem "B" .AddItem "C" .AddItem "D" .AddItem "E" .AddItem "F" End With End Sub Then since you're using the same options as the custom view names, you can use something like this in the combobox change event: Option Explicit Private Sub bgCmbox_Change() On Error Resume Next Me.Parent.CustomViews(bgCmbox.Value).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub MK wrote: Hello, I have trouble with linking combobox selection to the custom views I've created. Please help! In advance, thank you. I created an ActiveX control combobox and have teh following code: Dim bgCmbox As ComboBox Private Sub ComboBox1_Change() 'With Worksheets(1) Dim bgCmbox As ComboBox bgCmbox.AddItem " ", 1 bgCmbox.AddItem "(All)", 2 bgCmbox.AddItem "A", 3 bgCmbox.AddItem "B", 4 bgCmbox.AddItem "C", 5 bgCmbox.AddItem "D", 6 bgCmbox.AddItem "E", 7 bgCmbox.AddItem "F", 8 If bgCmbox.Value = "(All)" Then ActiveWorkbook.CustomViews("All").Show ElseIf bgCmbox.Value = "A" Then ActiveWorkbook.CustomViews("A").Show ElseIf bgCmbox.Value = "B" Then ActiveWorkbook.CustomViews("B").Show ElseIf bgCmbox.Value = "C" Then ActiveWorkbook.CustomViews("C").Show ElseIf bgCmbox.Value = "D" Then ActiveWorkbook.CustomViews("D").Show ElseIf bgCmbox.Value = "E" Then ActiveWorkbook.CustomViews("E").Show ElseIf bgCmbox.Value = "F" Then ActiveWorkbook.CustomViews("F").Show End If End Sub -- Dave Peterson . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
What's the name of the worksheet that owns the combobox?
What's the name of the combobox that you want to use? If you changed the code, post what you used and indicate what line caused the error. MK wrote: Thank you for the help but I still get a run-time error 91, saying that object variable or with block is not set. Can you advise? Thank you. "Dave Peterson" wrote: I would add the options to the combobox a single time--maybe in the workbook_open event? Option Explicit Private Sub Workbook_Open() With Worksheets("Sheet1").bgCmbox .Clear 'existing options .AddItem "(All)" .AddItem "A" .AddItem "B" .AddItem "C" .AddItem "D" .AddItem "E" .AddItem "F" End With End Sub Then since you're using the same options as the custom view names, you can use something like this in the combobox change event: Option Explicit Private Sub bgCmbox_Change() On Error Resume Next Me.Parent.CustomViews(bgCmbox.Value).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub MK wrote: Hello, I have trouble with linking combobox selection to the custom views I've created. Please help! In advance, thank you. I created an ActiveX control combobox and have teh following code: Dim bgCmbox As ComboBox Private Sub ComboBox1_Change() 'With Worksheets(1) Dim bgCmbox As ComboBox bgCmbox.AddItem " ", 1 bgCmbox.AddItem "(All)", 2 bgCmbox.AddItem "A", 3 bgCmbox.AddItem "B", 4 bgCmbox.AddItem "C", 5 bgCmbox.AddItem "D", 6 bgCmbox.AddItem "E", 7 bgCmbox.AddItem "F", 8 If bgCmbox.Value = "(All)" Then ActiveWorkbook.CustomViews("All").Show ElseIf bgCmbox.Value = "A" Then ActiveWorkbook.CustomViews("A").Show ElseIf bgCmbox.Value = "B" Then ActiveWorkbook.CustomViews("B").Show ElseIf bgCmbox.Value = "C" Then ActiveWorkbook.CustomViews("C").Show ElseIf bgCmbox.Value = "D" Then ActiveWorkbook.CustomViews("D").Show ElseIf bgCmbox.Value = "E" Then ActiveWorkbook.CustomViews("E").Show ElseIf bgCmbox.Value = "F" Then ActiveWorkbook.CustomViews("F").Show End If End Sub -- Dave Peterson . -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
Hi Dave,
I might be missing some declarations. But here it is. Thank you for your help! Dim cboView As ComboBox Option Explicit Private Sub Workbook_Open() With Worksheets("Sheet1").cboView .Clear 'existing options .AddItem "(All)" .AddItem "A" .AddItem "B" .AddItem "C" .AddItem "D" .AddItem "E" .AddItem "F" End With End Sub Option Explicit Private Sub cboView_Change() If cboView.Value = "(All)" Then ActiveWorkbook.CustomViews("All").Show ElseIf cboView.Value = "A" Then ActiveWorkbook.CustomViews("A").Show ElseIf cboView.Value = "B" Then ActiveWorkbook.CustomViews("B").Show ElseIf cboView.Value = "C" Then ActiveWorkbook.CustomViews("C").Show ElseIf cboView.Value = "D" Then ActiveWorkbook.CustomViews("D").Show ElseIf cboView.Value = "E" Then ActiveWorkbook.CustomViews("E").Show ElseIf cboView.Value = "F" Then ActiveWorkbook.CustomViews("F").Show End If End Sub "Dave Peterson" wrote: What's the name of the worksheet that owns the combobox? What's the name of the combobox that you want to use? If you changed the code, post what you used and indicate what line caused the error. MK wrote: Thank you for the help but I still get a run-time error 91, saying that object variable or with block is not set. Can you advise? Thank you. "Dave Peterson" wrote: I would add the options to the combobox a single time--maybe in the workbook_open event? Option Explicit Private Sub Workbook_Open() With Worksheets("Sheet1").bgCmbox .Clear 'existing options .AddItem "(All)" .AddItem "A" .AddItem "B" .AddItem "C" .AddItem "D" .AddItem "E" .AddItem "F" End With End Sub Then since you're using the same options as the custom view names, you can use something like this in the combobox change event: Option Explicit Private Sub bgCmbox_Change() On Error Resume Next Me.Parent.CustomViews(bgCmbox.Value).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub MK wrote: Hello, I have trouble with linking combobox selection to the custom views I've created. Please help! In advance, thank you. I created an ActiveX control combobox and have teh following code: Dim bgCmbox As ComboBox Private Sub ComboBox1_Change() 'With Worksheets(1) Dim bgCmbox As ComboBox bgCmbox.AddItem " ", 1 bgCmbox.AddItem "(All)", 2 bgCmbox.AddItem "A", 3 bgCmbox.AddItem "B", 4 bgCmbox.AddItem "C", 5 bgCmbox.AddItem "D", 6 bgCmbox.AddItem "E", 7 bgCmbox.AddItem "F", 8 If bgCmbox.Value = "(All)" Then ActiveWorkbook.CustomViews("All").Show ElseIf bgCmbox.Value = "A" Then ActiveWorkbook.CustomViews("A").Show ElseIf bgCmbox.Value = "B" Then ActiveWorkbook.CustomViews("B").Show ElseIf bgCmbox.Value = "C" Then ActiveWorkbook.CustomViews("C").Show ElseIf bgCmbox.Value = "D" Then ActiveWorkbook.CustomViews("D").Show ElseIf bgCmbox.Value = "E" Then ActiveWorkbook.CustomViews("E").Show ElseIf bgCmbox.Value = "F" Then ActiveWorkbook.CustomViews("F").Show End If End Sub -- Dave Peterson . -- Dave Peterson . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
Remove the declaration for cboView. You don't need it.
But you did have to name the combobox on Sheet1 cboView. Did you do that? And I see why you changed my suggestion for the _change procedure. I didn't notice the "(All)" vs "All" naming difference. You could use: Option Explicit Private Sub cboView_Change() Dim myStr as string myStr = me.cboview.value if lcase(mystr) = lcase("(all)") then myStr = "All" end if On Error Resume Next Me.Parent.CustomViews(myStr).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub I wouldn't want to use all those elseif's that end up using the view based on the name seen in the combobox. MK wrote: Hi Dave, I might be missing some declarations. But here it is. Thank you for your help! Dim cboView As ComboBox Option Explicit Private Sub Workbook_Open() With Worksheets("Sheet1").cboView .Clear 'existing options .AddItem "(All)" .AddItem "A" .AddItem "B" .AddItem "C" .AddItem "D" .AddItem "E" .AddItem "F" End With End Sub Option Explicit Private Sub cboView_Change() If cboView.Value = "(All)" Then ActiveWorkbook.CustomViews("All").Show ElseIf cboView.Value = "A" Then ActiveWorkbook.CustomViews("A").Show ElseIf cboView.Value = "B" Then ActiveWorkbook.CustomViews("B").Show ElseIf cboView.Value = "C" Then ActiveWorkbook.CustomViews("C").Show ElseIf cboView.Value = "D" Then ActiveWorkbook.CustomViews("D").Show ElseIf cboView.Value = "E" Then ActiveWorkbook.CustomViews("E").Show ElseIf cboView.Value = "F" Then ActiveWorkbook.CustomViews("F").Show End If End Sub "Dave Peterson" wrote: What's the name of the worksheet that owns the combobox? What's the name of the combobox that you want to use? If you changed the code, post what you used and indicate what line caused the error. MK wrote: Thank you for the help but I still get a run-time error 91, saying that object variable or with block is not set. Can you advise? Thank you. "Dave Peterson" wrote: I would add the options to the combobox a single time--maybe in the workbook_open event? Option Explicit Private Sub Workbook_Open() With Worksheets("Sheet1").bgCmbox .Clear 'existing options .AddItem "(All)" .AddItem "A" .AddItem "B" .AddItem "C" .AddItem "D" .AddItem "E" .AddItem "F" End With End Sub Then since you're using the same options as the custom view names, you can use something like this in the combobox change event: Option Explicit Private Sub bgCmbox_Change() On Error Resume Next Me.Parent.CustomViews(bgCmbox.Value).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub MK wrote: Hello, I have trouble with linking combobox selection to the custom views I've created. Please help! In advance, thank you. I created an ActiveX control combobox and have teh following code: Dim bgCmbox As ComboBox Private Sub ComboBox1_Change() 'With Worksheets(1) Dim bgCmbox As ComboBox bgCmbox.AddItem " ", 1 bgCmbox.AddItem "(All)", 2 bgCmbox.AddItem "A", 3 bgCmbox.AddItem "B", 4 bgCmbox.AddItem "C", 5 bgCmbox.AddItem "D", 6 bgCmbox.AddItem "E", 7 bgCmbox.AddItem "F", 8 If bgCmbox.Value = "(All)" Then ActiveWorkbook.CustomViews("All").Show ElseIf bgCmbox.Value = "A" Then ActiveWorkbook.CustomViews("A").Show ElseIf bgCmbox.Value = "B" Then ActiveWorkbook.CustomViews("B").Show ElseIf bgCmbox.Value = "C" Then ActiveWorkbook.CustomViews("C").Show ElseIf bgCmbox.Value = "D" Then ActiveWorkbook.CustomViews("D").Show ElseIf bgCmbox.Value = "E" Then ActiveWorkbook.CustomViews("E").Show ElseIf bgCmbox.Value = "F" Then ActiveWorkbook.CustomViews("F").Show End If End Sub -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Custom Views | Setting up and Configuration of Excel | |||
| Custom Views | Excel Discussion (Misc queries) | |||
| Custom Views | New Users to Excel | |||
| custom views | Excel Worksheet Functions | |||
| Custom views | Excel Discussion (Misc queries) | |||
Hybrid Mode
