Load Sheet Names into Combobox Except...

[breynolds0431]

hello. trying to add sheet names to a userform combobox that are only visible, are not named "Start", and do not include a hyphen in the name.

I've tried the following, but that still adds all visible sheets that are not named "Start". If I change the second And to an Or, it loads all hidden sheets.

Any help on how to only add visible sheets that are not named Start and do not include a hyphen in the name?

 Dim ws As Worksheet
          For Each ws In ActiveWorkbook.Sheets
          If ws.Visible = True And ws.Name <> "Start" And Not InStr(ws.Name, "-") Then cboSheets.AddItem ws.Name
 Next

 

[johnnyL]

What?

You say ...
'I've tried the following, but that still adds all visible sheets that are not named "Start"'
Then you say ...
'Any help on how to only add visible sheets that are not named Start'

Can you confirm what your intentions are?


That being said I think you want:

        If ws.Visible = True And ws.Name <> "Start" And InStr(ws.Name, "-") = 0 Then

 

[Norie]

You can easily check if the name doesn't contain '-' by checking if InStr returns 0.

    For Each ws In ActiveWorkbook.Sheets
        If ws.Visible = xlSheetVisible And ws.Name <> "Start" And InStr(ws.Name, "-") = 0 Then
            ComboBox1.AddItem ws.Name
        End If
    Next ws

 

[johnnyL]

You can easily check if the name doesn't contain '-' by checking if InStr returns 0.

Why didn't I think of that?

 

[breynolds0431]

You can easily check if the name doesn't contain '-' by checking if InStr returns 0.

    For Each ws In ActiveWorkbook.Sheets
        If ws.Visible = xlSheetVisible And ws.Name <> "Start" And InStr(ws.Name, "-") = 0 Then
            ComboBox1.AddItem ws.Name
        End If
    Next ws

Thanks, @Norie!