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!"