| 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
Hi Dave,
I keep adjusting the code based on some of your feedback and also what I've found in the forum. This is the code I have and for some reason I get an error that the object doesn't support the property/method. Again, thank you very much for your help! Sub cboView_Change() Dim views As Range Set views = Sheet5("Control").Range("views") Me.cboView.List = views.Value ' Me.cboView.RowSource = views.Address(external:=True) On Error Resume Next Me.Parent.CustomViews(views).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub "Dave Peterson" wrote: 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 . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
Dave,
I actually figured it out. My code is: Option Explicit Sub cboView_Change() Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") cboView.List = views.Value For Each i In views ThisWorkbook.CustomViews(cboView.Value).Show Exit For Next i Reset End Sub The only thing i'd like to add is to set "All" view as default. Could you help me out there? Thank you, Maria "MK" wrote: Hi Dave, I keep adjusting the code based on some of your feedback and also what I've found in the forum. This is the code I have and for some reason I get an error that the object doesn't support the property/method. Again, thank you very much for your help! Sub cboView_Change() Dim views As Range Set views = Sheet5("Control").Range("views") Me.cboView.List = views.Value ' Me.cboView.RowSource = views.Address(external:=True) On Error Resume Next Me.Parent.CustomViews(views).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub "Dave Peterson" wrote: 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 . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
First, you have to tell me why this didn't work:
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 After you do that, I'll show you how to use: 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" .ListIndex = 0 'show the first item in the dropdown End With End Sub MK wrote: Dave, I actually figured it out. My code is: Option Explicit Sub cboView_Change() Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") cboView.List = views.Value For Each i In views ThisWorkbook.CustomViews(cboView.Value).Show Exit For Next i Reset End Sub The only thing i'd like to add is to set "All" view as default. Could you help me out there? Thank you, Maria "MK" wrote: Hi Dave, I keep adjusting the code based on some of your feedback and also what I've found in the forum. This is the code I have and for some reason I get an error that the object doesn't support the property/method. Again, thank you very much for your help! Sub cboView_Change() Dim views As Range Set views = Sheet5("Control").Range("views") Me.cboView.List = views.Value ' Me.cboView.RowSource = views.Address(external:=True) On Error Resume Next Me.Parent.CustomViews(views).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub "Dave Peterson" wrote: 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 . -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
Hi Dave,
When I ran the code for "Private Sub Cbo_View...", I didn't have anything in the drop down box to choose from. So, I kept getting the error message. "Dave Peterson" wrote: First, you have to tell me why this didn't work: 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 After you do that, I'll show you how to use: 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" .ListIndex = 0 'show the first item in the dropdown End With End Sub MK wrote: Dave, I actually figured it out. My code is: Option Explicit Sub cboView_Change() Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") cboView.List = views.Value For Each i In views ThisWorkbook.CustomViews(cboView.Value).Show Exit For Next i Reset End Sub The only thing i'd like to add is to set "All" view as default. Could you help me out there? Thank you, Maria "MK" wrote: Hi Dave, I keep adjusting the code based on some of your feedback and also what I've found in the forum. This is the code I have and for some reason I get an error that the object doesn't support the property/method. Again, thank you very much for your help! Sub cboView_Change() Dim views As Range Set views = Sheet5("Control").Range("views") Me.cboView.List = views.Value ' Me.cboView.RowSource = views.Address(external:=True) On Error Resume Next Me.Parent.CustomViews(views).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub "Dave Peterson" wrote: 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 . |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
The combobox was populated in the workbook_Open event.
Did you allow macros to run when you opened the workbook? MK wrote: Hi Dave, When I ran the code for "Private Sub Cbo_View...", I didn't have anything in the drop down box to choose from. So, I kept getting the error message. "Dave Peterson" wrote: First, you have to tell me why this didn't work: 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 After you do that, I'll show you how to use: 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" .ListIndex = 0 'show the first item in the dropdown End With End Sub MK wrote: Dave, I actually figured it out. My code is: Option Explicit Sub cboView_Change() Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") cboView.List = views.Value For Each i In views ThisWorkbook.CustomViews(cboView.Value).Show Exit For Next i Reset End Sub The only thing i'd like to add is to set "All" view as default. Could you help me out there? Thank you, Maria "MK" wrote: Hi Dave, I keep adjusting the code based on some of your feedback and also what I've found in the forum. This is the code I have and for some reason I get an error that the object doesn't support the property/method. Again, thank you very much for your help! Sub cboView_Change() Dim views As Range Set views = Sheet5("Control").Range("views") Me.cboView.List = views.Value ' Me.cboView.RowSource = views.Address(external:=True) On Error Resume Next Me.Parent.CustomViews(views).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub "Dave Peterson" wrote: 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
I ran my Private sub cboView_Change in sheet 1 and it worked well. However, I
had to initiate a macro in order for it to work. When I copied the code into ActiveWorksheet, it didn't work. This is what I have copied over with some edits. Private Sub Workbook_Open() Dim cboView As ComboBox Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") 'cboView.List = views.Value For Each i In views Me.Sheets(1).CustomViews(cboView.Value).Show Exit For Next i End Sub I get error '91, Object or with variable is missing. PURPOSE OF THE MACRO: I want it to run such that when the user selects the view, that view is displayed. Also, I would like to set it up in a such a way that the user doesn't have to initiate any macros but can use the combobox once the worksheet is opened. Thank you very much for all your help with this!!! Best, MK "Dave Peterson" wrote: The combobox was populated in the workbook_Open event. Did you allow macros to run when you opened the workbook? MK wrote: Hi Dave, When I ran the code for "Private Sub Cbo_View...", I didn't have anything in the drop down box to choose from. So, I kept getting the error message. "Dave Peterson" wrote: First, you have to tell me why this didn't work: 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 After you do that, I'll show you how to use: 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" .ListIndex = 0 'show the first item in the dropdown End With End Sub MK wrote: Dave, I actually figured it out. My code is: Option Explicit Sub cboView_Change() Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") cboView.List = views.Value For Each i In views ThisWorkbook.CustomViews(cboView.Value).Show Exit For Next i Reset End Sub The only thing i'd like to add is to set "All" view as default. Could you help me out there? Thank you, Maria "MK" wrote: Hi Dave, I keep adjusting the code based on some of your feedback and also what I've found in the forum. This is the code I have and for some reason I get an error that the object doesn't support the property/method. Again, thank you very much for your help! Sub cboView_Change() Dim views As Range Set views = Sheet5("Control").Range("views") Me.cboView.List = views.Value ' Me.cboView.RowSource = views.Address(external:=True) On Error Resume Next Me.Parent.CustomViews(views).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub "Dave Peterson" wrote: 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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
I don't understand what you're doing in the workbook_open event.
I think you'll need to give more info. MK wrote: I ran my Private sub cboView_Change in sheet 1 and it worked well. However, I had to initiate a macro in order for it to work. When I copied the code into ActiveWorksheet, it didn't work. This is what I have copied over with some edits. Private Sub Workbook_Open() Dim cboView As ComboBox Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") 'cboView.List = views.Value For Each i In views Me.Sheets(1).CustomViews(cboView.Value).Show Exit For Next i End Sub I get error '91, Object or with variable is missing. PURPOSE OF THE MACRO: I want it to run such that when the user selects the view, that view is displayed. Also, I would like to set it up in a such a way that the user doesn't have to initiate any macros but can use the combobox once the worksheet is opened. Thank you very much for all your help with this!!! Best, MK "Dave Peterson" wrote: The combobox was populated in the workbook_Open event. Did you allow macros to run when you opened the workbook? MK wrote: Hi Dave, When I ran the code for "Private Sub Cbo_View...", I didn't have anything in the drop down box to choose from. So, I kept getting the error message. "Dave Peterson" wrote: First, you have to tell me why this didn't work: 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 After you do that, I'll show you how to use: 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" .ListIndex = 0 'show the first item in the dropdown End With End Sub MK wrote: Dave, I actually figured it out. My code is: Option Explicit Sub cboView_Change() Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") cboView.List = views.Value For Each i In views ThisWorkbook.CustomViews(cboView.Value).Show Exit For Next i Reset End Sub The only thing i'd like to add is to set "All" view as default. Could you help me out there? Thank you, Maria "MK" wrote: Hi Dave, I keep adjusting the code based on some of your feedback and also what I've found in the forum. This is the code I have and for some reason I get an error that the object doesn't support the property/method. Again, thank you very much for your help! Sub cboView_Change() Dim views As Range Set views = Sheet5("Control").Range("views") Me.cboView.List = views.Value ' Me.cboView.RowSource = views.Address(external:=True) On Error Resume Next Me.Parent.CustomViews(views).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub "Dave Peterson" wrote: 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 -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
I want the combobox to work when the user opens the workbook. I don't want
the user to go through additional steps of running any macros, but rather make the combobox functional as the workbook is opened. In the workbook_open sub I just copied over the code that originally was in Sheet1 under sub cboView_Change(). The code worked in Sheet1 sub cboView__Change, but the user had to run the cboView_Change macro for it to function. As I stated above, I really want to avoid that. Could you please advise? Thank you for all your help!!! MK "Dave Peterson" wrote: I don't understand what you're doing in the workbook_open event. I think you'll need to give more info. MK wrote: I ran my Private sub cboView_Change in sheet 1 and it worked well. However, I had to initiate a macro in order for it to work. When I copied the code into ActiveWorksheet, it didn't work. This is what I have copied over with some edits. Private Sub Workbook_Open() Dim cboView As ComboBox Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") 'cboView.List = views.Value For Each i In views Me.Sheets(1).CustomViews(cboView.Value).Show Exit For Next i End Sub I get error '91, Object or with variable is missing. PURPOSE OF THE MACRO: I want it to run such that when the user selects the view, that view is displayed. Also, I would like to set it up in a such a way that the user doesn't have to initiate any macros but can use the combobox once the worksheet is opened. Thank you very much for all your help with this!!! Best, MK "Dave Peterson" wrote: The combobox was populated in the workbook_Open event. Did you allow macros to run when you opened the workbook? MK wrote: Hi Dave, When I ran the code for "Private Sub Cbo_View...", I didn't have anything in the drop down box to choose from. So, I kept getting the error message. "Dave Peterson" wrote: First, you have to tell me why this didn't work: 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 After you do that, I'll show you how to use: 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" .ListIndex = 0 'show the first item in the dropdown End With End Sub MK wrote: Dave, I actually figured it out. My code is: Option Explicit Sub cboView_Change() Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") cboView.List = views.Value For Each i In views ThisWorkbook.CustomViews(cboView.Value).Show Exit For Next i Reset End Sub The only thing i'd like to add is to set "All" view as default. Could you help me out there? Thank you, Maria "MK" wrote: Hi Dave, I keep adjusting the code based on some of your feedback and also what I've found in the forum. This is the code I have and for some reason I get an error that the object doesn't support the property/method. Again, thank you very much for your help! Sub cboView_Change() Dim views As Range Set views = Sheet5("Control").Range("views") Me.cboView.List = views.Value ' Me.cboView.RowSource = views.Address(external:=True) On Error Resume Next Me.Parent.CustomViews(views).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub "Dave Peterson" wrote: 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!" |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
I thought that cboView was a combobox that displayed the custom view that the
user chose. That's why you were using the cboView_Change event. If you want the Workbook_Open event to show a particular custom view (not loop through them all), then just show the custom view that you want. Option Explicit Private Sub Workbook_Open() Me.CustomViews("whateverviewyouwant").Show End sub MK wrote: I want the combobox to work when the user opens the workbook. I don't want the user to go through additional steps of running any macros, but rather make the combobox functional as the workbook is opened. In the workbook_open sub I just copied over the code that originally was in Sheet1 under sub cboView_Change(). The code worked in Sheet1 sub cboView__Change, but the user had to run the cboView_Change macro for it to function. As I stated above, I really want to avoid that. Could you please advise? Thank you for all your help!!! MK "Dave Peterson" wrote: I don't understand what you're doing in the workbook_open event. I think you'll need to give more info. MK wrote: I ran my Private sub cboView_Change in sheet 1 and it worked well. However, I had to initiate a macro in order for it to work. When I copied the code into ActiveWorksheet, it didn't work. This is what I have copied over with some edits. Private Sub Workbook_Open() Dim cboView As ComboBox Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") 'cboView.List = views.Value For Each i In views Me.Sheets(1).CustomViews(cboView.Value).Show Exit For Next i End Sub I get error '91, Object or with variable is missing. PURPOSE OF THE MACRO: I want it to run such that when the user selects the view, that view is displayed. Also, I would like to set it up in a such a way that the user doesn't have to initiate any macros but can use the combobox once the worksheet is opened. Thank you very much for all your help with this!!! Best, MK "Dave Peterson" wrote: The combobox was populated in the workbook_Open event. Did you allow macros to run when you opened the workbook? MK wrote: Hi Dave, When I ran the code for "Private Sub Cbo_View...", I didn't have anything in the drop down box to choose from. So, I kept getting the error message. "Dave Peterson" wrote: First, you have to tell me why this didn't work: 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 After you do that, I'll show you how to use: 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" .ListIndex = 0 'show the first item in the dropdown End With End Sub MK wrote: Dave, I actually figured it out. My code is: Option Explicit Sub cboView_Change() Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") cboView.List = views.Value For Each i In views ThisWorkbook.CustomViews(cboView.Value).Show Exit For Next i Reset End Sub The only thing i'd like to add is to set "All" view as default. Could you help me out there? Thank you, Maria "MK" wrote: Hi Dave, I keep adjusting the code based on some of your feedback and also what I've found in the forum. This is the code I have and for some reason I get an error that the object doesn't support the property/method. Again, thank you very much for your help! Sub cboView_Change() Dim views As Range Set views = Sheet5("Control").Range("views") Me.cboView.List = views.Value ' Me.cboView.RowSource = views.Address(external:=True) On Error Resume Next Me.Parent.CustomViews(views).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub "Dave Peterson" wrote: 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!" -- Dave Peterson |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
Thank you, Dave. It seems like I'm making this super complicated.
I do want to show different custom views based on user preference. However, I want to set up the workbook, such that the user doesn't have to run a macro. Instead, I would like the combobox be functional once the user opens the workbook. Is it something feasible? Again, thank you for all your help!!! "Dave Peterson" wrote: I thought that cboView was a combobox that displayed the custom view that the user chose. That's why you were using the cboView_Change event. If you want the Workbook_Open event to show a particular custom view (not loop through them all), then just show the custom view that you want. Option Explicit Private Sub Workbook_Open() Me.CustomViews("whateverviewyouwant").Show End sub MK wrote: I want the combobox to work when the user opens the workbook. I don't want the user to go through additional steps of running any macros, but rather make the combobox functional as the workbook is opened. In the workbook_open sub I just copied over the code that originally was in Sheet1 under sub cboView_Change(). The code worked in Sheet1 sub cboView__Change, but the user had to run the cboView_Change macro for it to function. As I stated above, I really want to avoid that. Could you please advise? Thank you for all your help!!! MK "Dave Peterson" wrote: I don't understand what you're doing in the workbook_open event. I think you'll need to give more info. MK wrote: I ran my Private sub cboView_Change in sheet 1 and it worked well. However, I had to initiate a macro in order for it to work. When I copied the code into ActiveWorksheet, it didn't work. This is what I have copied over with some edits. Private Sub Workbook_Open() Dim cboView As ComboBox Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") 'cboView.List = views.Value For Each i In views Me.Sheets(1).CustomViews(cboView.Value).Show Exit For Next i End Sub I get error '91, Object or with variable is missing. PURPOSE OF THE MACRO: I want it to run such that when the user selects the view, that view is displayed. Also, I would like to set it up in a such a way that the user doesn't have to initiate any macros but can use the combobox once the worksheet is opened. Thank you very much for all your help with this!!! Best, MK "Dave Peterson" wrote: The combobox was populated in the workbook_Open event. Did you allow macros to run when you opened the workbook? MK wrote: Hi Dave, When I ran the code for "Private Sub Cbo_View...", I didn't have anything in the drop down box to choose from. So, I kept getting the error message. "Dave Peterson" wrote: First, you have to tell me why this didn't work: 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 After you do that, I'll show you how to use: 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" .ListIndex = 0 'show the first item in the dropdown End With End Sub MK wrote: Dave, I actually figured it out. My code is: Option Explicit Sub cboView_Change() Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") cboView.List = views.Value For Each i In views ThisWorkbook.CustomViews(cboView.Value).Show Exit For Next i Reset End Sub The only thing i'd like to add is to set "All" view as default. Could you help me out there? Thank you, Maria "MK" wrote: Hi Dave, I keep adjusting the code based on some of your feedback and also what I've found in the forum. This is the code I have and for some reason I get an error that the object doesn't support the property/method. Again, thank you very much for your help! Sub cboView_Change() Dim views As Range Set views = Sheet5("Control").Range("views") Me.cboView.List = views.Value ' Me.cboView.RowSource = views.Address(external:=True) On Error Resume Next Me.Parent.CustomViews(views).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub "Dave Peterson" wrote: 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: |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
I don't understand why changing the combobox on that sheet isn't functional when
the workbook opens. When/how do you populate the combobox? MK wrote: Thank you, Dave. It seems like I'm making this super complicated. I do want to show different custom views based on user preference. However, I want to set up the workbook, such that the user doesn't have to run a macro. Instead, I would like the combobox be functional once the user opens the workbook. Is it something feasible? Again, thank you for all your help!!! "Dave Peterson" wrote: I thought that cboView was a combobox that displayed the custom view that the user chose. That's why you were using the cboView_Change event. If you want the Workbook_Open event to show a particular custom view (not loop through them all), then just show the custom view that you want. Option Explicit Private Sub Workbook_Open() Me.CustomViews("whateverviewyouwant").Show End sub MK wrote: I want the combobox to work when the user opens the workbook. I don't want the user to go through additional steps of running any macros, but rather make the combobox functional as the workbook is opened. In the workbook_open sub I just copied over the code that originally was in Sheet1 under sub cboView_Change(). The code worked in Sheet1 sub cboView__Change, but the user had to run the cboView_Change macro for it to function. As I stated above, I really want to avoid that. Could you please advise? Thank you for all your help!!! MK "Dave Peterson" wrote: I don't understand what you're doing in the workbook_open event. I think you'll need to give more info. MK wrote: I ran my Private sub cboView_Change in sheet 1 and it worked well. However, I had to initiate a macro in order for it to work. When I copied the code into ActiveWorksheet, it didn't work. This is what I have copied over with some edits. Private Sub Workbook_Open() Dim cboView As ComboBox Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") 'cboView.List = views.Value For Each i In views Me.Sheets(1).CustomViews(cboView.Value).Show Exit For Next i End Sub I get error '91, Object or with variable is missing. PURPOSE OF THE MACRO: I want it to run such that when the user selects the view, that view is displayed. Also, I would like to set it up in a such a way that the user doesn't have to initiate any macros but can use the combobox once the worksheet is opened. Thank you very much for all your help with this!!! Best, MK "Dave Peterson" wrote: The combobox was populated in the workbook_Open event. Did you allow macros to run when you opened the workbook? MK wrote: Hi Dave, When I ran the code for "Private Sub Cbo_View...", I didn't have anything in the drop down box to choose from. So, I kept getting the error message. "Dave Peterson" wrote: First, you have to tell me why this didn't work: 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 After you do that, I'll show you how to use: 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" .ListIndex = 0 'show the first item in the dropdown End With End Sub MK wrote: Dave, I actually figured it out. My code is: Option Explicit Sub cboView_Change() Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") cboView.List = views.Value For Each i In views ThisWorkbook.CustomViews(cboView.Value).Show Exit For Next i Reset End Sub The only thing i'd like to add is to set "All" view as default. Could you help me out there? Thank you, Maria "MK" wrote: Hi Dave, I keep adjusting the code based on some of your feedback and also what I've found in the forum. This is the code I have and for some reason I get an error that the object doesn't support the property/method. Again, thank you very much for your help! Sub cboView_Change() Dim views As Range Set views = Sheet5("Control").Range("views") Me.cboView.List = views.Value ' Me.cboView.RowSource = views.Address(external:=True) On Error Resume Next Me.Parent.CustomViews(views).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub "Dave Peterson" wrote: 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: -- Dave Peterson |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
When I run the Private Sub cboView_Change() in Sheet1 (where it is actually
located), I have to run the macro first for it to function. I would like to see if I can run the combobox with its custom views without going to Developer Ribbon Macros Run Sheet1.cboView_Change() macro. When I copy the code into "ThisWorkbook" I get run-time error 91 (object or variable is not set). Public Sub Workbook_Open() Dim cboView as Combobox Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") cboView.List = views.Value For Each i In views ActiveWorkbook.CustomViews(cboView.Value).Show Exit For Next i End Sub "Dave Peterson" wrote: I don't understand why changing the combobox on that sheet isn't functional when the workbook opens. When/how do you populate the combobox? MK wrote: Thank you, Dave. It seems like I'm making this super complicated. I do want to show different custom views based on user preference. However, I want to set up the workbook, such that the user doesn't have to run a macro. Instead, I would like the combobox be functional once the user opens the workbook. Is it something feasible? Again, thank you for all your help!!! "Dave Peterson" wrote: I thought that cboView was a combobox that displayed the custom view that the user chose. That's why you were using the cboView_Change event. If you want the Workbook_Open event to show a particular custom view (not loop through them all), then just show the custom view that you want. Option Explicit Private Sub Workbook_Open() Me.CustomViews("whateverviewyouwant").Show End sub MK wrote: I want the combobox to work when the user opens the workbook. I don't want the user to go through additional steps of running any macros, but rather make the combobox functional as the workbook is opened. In the workbook_open sub I just copied over the code that originally was in Sheet1 under sub cboView_Change(). The code worked in Sheet1 sub cboView__Change, but the user had to run the cboView_Change macro for it to function. As I stated above, I really want to avoid that. Could you please advise? Thank you for all your help!!! MK "Dave Peterson" wrote: I don't understand what you're doing in the workbook_open event. I think you'll need to give more info. MK wrote: I ran my Private sub cboView_Change in sheet 1 and it worked well. However, I had to initiate a macro in order for it to work. When I copied the code into ActiveWorksheet, it didn't work. This is what I have copied over with some edits. Private Sub Workbook_Open() Dim cboView As ComboBox Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") 'cboView.List = views.Value For Each i In views Me.Sheets(1).CustomViews(cboView.Value).Show Exit For Next i End Sub I get error '91, Object or with variable is missing. PURPOSE OF THE MACRO: I want it to run such that when the user selects the view, that view is displayed. Also, I would like to set it up in a such a way that the user doesn't have to initiate any macros but can use the combobox once the worksheet is opened. Thank you very much for all your help with this!!! Best, MK "Dave Peterson" wrote: The combobox was populated in the workbook_Open event. Did you allow macros to run when you opened the workbook? MK wrote: Hi Dave, When I ran the code for "Private Sub Cbo_View...", I didn't have anything in the drop down box to choose from. So, I kept getting the error message. "Dave Peterson" wrote: First, you have to tell me why this didn't work: 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 After you do that, I'll show you how to use: 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" .ListIndex = 0 'show the first item in the dropdown End With End Sub MK wrote: Dave, I actually figured it out. My code is: Option Explicit Sub cboView_Change() Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") cboView.List = views.Value For Each i In views ThisWorkbook.CustomViews(cboView.Value).Show Exit For Next i Reset End Sub The only thing i'd like to add is to set "All" view as default. Could you help me out there? Thank you, Maria "MK" wrote: Hi Dave, I keep adjusting the code based on some of your feedback and also what I've found in the forum. This is the code I have and for some reason I get an error that the object doesn't support the property/method. Again, thank you very much for your help! Sub cboView_Change() Dim views As Range Set views = Sheet5("Control").Range("views") Me.cboView.List = views.Value ' Me.cboView.RowSource = views.Address(external:=True) On Error Resume Next Me.Parent.CustomViews(views).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub "Dave Peterson" wrote: 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? |
|
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combobox linked to Custom Views
Go back to the first suggestion.
It had a workbook_open procedure that belongs in the ThisWorkbook module. 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 the code behind the worksheet that owns that combobox should look like this: 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 The only thing that the user has to do is allow macros to run. MK wrote: When I run the Private Sub cboView_Change() in Sheet1 (where it is actually located), I have to run the macro first for it to function. I would like to see if I can run the combobox with its custom views without going to Developer Ribbon Macros Run Sheet1.cboView_Change() macro. When I copy the code into "ThisWorkbook" I get run-time error 91 (object or variable is not set). Public Sub Workbook_Open() Dim cboView as Combobox Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") cboView.List = views.Value For Each i In views ActiveWorkbook.CustomViews(cboView.Value).Show Exit For Next i End Sub "Dave Peterson" wrote: I don't understand why changing the combobox on that sheet isn't functional when the workbook opens. When/how do you populate the combobox? MK wrote: Thank you, Dave. It seems like I'm making this super complicated. I do want to show different custom views based on user preference. However, I want to set up the workbook, such that the user doesn't have to run a macro. Instead, I would like the combobox be functional once the user opens the workbook. Is it something feasible? Again, thank you for all your help!!! "Dave Peterson" wrote: I thought that cboView was a combobox that displayed the custom view that the user chose. That's why you were using the cboView_Change event. If you want the Workbook_Open event to show a particular custom view (not loop through them all), then just show the custom view that you want. Option Explicit Private Sub Workbook_Open() Me.CustomViews("whateverviewyouwant").Show End sub MK wrote: I want the combobox to work when the user opens the workbook. I don't want the user to go through additional steps of running any macros, but rather make the combobox functional as the workbook is opened. In the workbook_open sub I just copied over the code that originally was in Sheet1 under sub cboView_Change(). The code worked in Sheet1 sub cboView__Change, but the user had to run the cboView_Change macro for it to function. As I stated above, I really want to avoid that. Could you please advise? Thank you for all your help!!! MK "Dave Peterson" wrote: I don't understand what you're doing in the workbook_open event. I think you'll need to give more info. MK wrote: I ran my Private sub cboView_Change in sheet 1 and it worked well. However, I had to initiate a macro in order for it to work. When I copied the code into ActiveWorksheet, it didn't work. This is what I have copied over with some edits. Private Sub Workbook_Open() Dim cboView As ComboBox Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") 'cboView.List = views.Value For Each i In views Me.Sheets(1).CustomViews(cboView.Value).Show Exit For Next i End Sub I get error '91, Object or with variable is missing. PURPOSE OF THE MACRO: I want it to run such that when the user selects the view, that view is displayed. Also, I would like to set it up in a such a way that the user doesn't have to initiate any macros but can use the combobox once the worksheet is opened. Thank you very much for all your help with this!!! Best, MK "Dave Peterson" wrote: The combobox was populated in the workbook_Open event. Did you allow macros to run when you opened the workbook? MK wrote: Hi Dave, When I ran the code for "Private Sub Cbo_View...", I didn't have anything in the drop down box to choose from. So, I kept getting the error message. "Dave Peterson" wrote: First, you have to tell me why this didn't work: 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 After you do that, I'll show you how to use: 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" .ListIndex = 0 'show the first item in the dropdown End With End Sub MK wrote: Dave, I actually figured it out. My code is: Option Explicit Sub cboView_Change() Dim views As Range Dim i As Variant i = Array(views) Set views = Sheet5.Range("views") cboView.List = views.Value For Each i In views ThisWorkbook.CustomViews(cboView.Value).Show Exit For Next i Reset End Sub The only thing i'd like to add is to set "All" view as default. Could you help me out there? Thank you, Maria "MK" wrote: Hi Dave, I keep adjusting the code based on some of your feedback and also what I've found in the forum. This is the code I have and for some reason I get an error that the object doesn't support the property/method. Again, thank you very much for your help! Sub cboView_Change() Dim views As Range Set views = Sheet5("Control").Range("views") Me.cboView.List = views.Value ' Me.cboView.RowSource = views.Address(external:=True) On Error Resume Next Me.Parent.CustomViews(views).Show If Err.Number < 0 Then Err.Clear MsgBox "Design error!" End If On Error GoTo 0 End Sub "Dave Peterson" wrote: 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? -- 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) | |||
Linear Mode
