EXCEL: Array and combobox questions

MsgBox ComboBox1.Text

dim e as integer, stoR 
stoR = 0

on error resume next
stoR = Application.Match(Sh.Name, arPayPeriods, 0)
e = err.number
on error goto 0

if e = 0 then 'this will replace the line> If Application.Match(Sh.Name, arPayPeriods, 0) Then
else 'this is new - capturing error
         msgbox sh.name & " not found!"
end if

Select allOpen in new window

@Shanan2012,

application.match will return an error value if the lookup item is not found, not raise a runtime error, so err.number will still be 0.

Thanks Rorya on clearing that. Gpchicago08, please ignore my solution!

@rorya
Thanks!  That worked perfectly.

Please forgive me as I'm not fully versed in VBA, can you give a quick example or explanation of the flag variable??

Sure. At the top of a normal module add something like

Public bSkipEvents as Boolean

Select allOpen in new window

Then your change event needs to start with

If bSkipEvents Then Exit Sub

Select allOpen in new window

and your code to reset/populate the list needs:

bSkipEvents = True

Select allOpen in new window

at the top of it and then

bSkipEvents = False

Select allOpen in new window

at the end so that, even though the change event is still called when you reset the list, the code simply exits immediately and returns to the code that populates the control.

Does that make sense?

Perfect

Here's the code I'm using for the second part of my question - the _Click event.

I'm getting and Object Required error for line 4.

Private Sub cboSelectPP_Click()
MsgBox cboSelectPP.Text
  Application.ScreenUpdating = False
  Activeworksheet.Range("F16:F41").Value = Worksheets(cboSelectPP.Text).Range("F16:F41").Value
  Application.ScreenUpdating = True
End Sub

Select allOpen in new window

What is the value of cboSelectPP.Text?

Text "Jan-1-15"

I'm assuming I would need the CodeName (Sheet6) of the worksheet?

Try it again. When the error happens at line 4 type Ctrl+g to view the Immediate Window and type cboSelectPP.Text = "Sheet6" and then press F5. Does it work?

No.  Doesn't work. Still get the same error.

I'm surprised. Maybe there's something else going on but are you aware that you can have your combobox contain more that one column? Here's an example where it is set up to have 2 columns but only the first column visible. If it's not too much trouble you could try this with your sheet names.

    Dim MyArray(2, 1) As String

    MyArray(0, 0) = "Sheet1"
    MyArray(0, 1) = "Test"
    MyArray(1, 0) = "Sheet2"
    MyArray(1, 1) = "Test2"
    MyArray(2, 0) = "Sheet3"
    MyArray(2, 1) = "Test3"
 
    With cboSelectPP
        .ColumnCount = 2
        .ColumnWidths = "80;0"
        .List = MyArray
    End With
    MsgBox cboSelectPP.List(0, 1)
    'or
   'cboSelectPP.List(cboSelectPP.ListIndex, 1)

Select allOpen in new window

Thanks again rorya!!!  Simple mistake on my part!  It works!