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

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

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
> .
>

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

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
> .
>

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

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,

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
> > .
> >

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

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
> > > > > > > > .
> > > > > > > >