Combobox linked to Custom Views

[MK]

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]

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]

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]

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]

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]

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]

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

 

[MK]

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

 

[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]

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]

The combobox was populated in the workbook_Open event.

Did you allow macros to run when you opened the workbook?

 

[Dave Peterson]

I don't understand what you're doing in the workbook_open event.

I think you'll need to give more info.

 

[MK]

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]

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]

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]

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]

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]

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.