Populate three different combobox on different WS with same VBA code

[ron2k]

Hi guys,

I have a combobox (COMBOBOX1) on each of three different worksheets ("Tab1", "Tab2", "Tab3"). Each combobox is populated by the same code. However, in my current settings I have the same code written three times in the "ThisWorkbook" variable. For example:

Private Sub Workbook_Open()
  With ThisWorkbook.Sheets("Tab1").ComboBox1
   '[MY IDENTICAL CODE]
  End With

  With ThisWorkbook.Sheets("Tab2").ComboBox1
   '[MY IDENTICAL CODE]
  End With

  With ThisWorkbook.Sheets("Tab3").ComboBox1
    '[MY IDENTICAL CODE]
   End With

This adds to the sluggishness of my workbook. Is there a way to combine my code to affect all three comboboxes at the same time?

Thanks,

 

[ron2k]

If this provides more info, this is what I essentially have:

Private Sub Workbook_Open()
  With ThisWorkbook.Worksheets("Tab1").ComboBox1
    Select Case Environ$("USERNAME")
    Case "ron2k_1", "frisbyt", "terryg", "tomcat"
      .AddItem ""
      .AddItem "Frig"
      .AddItem "Tel"
      .AddItem "Dull"
      .AddItem "Bug"
    Case "mguss", "qjang"
      .AddItem ""
      .AddItem "Dang"
      .AddItem "Conk"
    Case "errop"
      .AddItem ""
      .AddItem "Plast"
      .AddItem "teep"
    End Select
  End With

With ThisWorkbook.Worksheets("Tab2").ComboBox1
    Select Case Environ$("USERNAME")
    Case "ron2k_1", "frisbyt", "terryg", "tomcat"
      .AddItem ""
      .AddItem "Frig"
      .AddItem "Tel"
      .AddItem "Dull"
      .AddItem "Bug"
    Case "mguss", "qjang"
      .AddItem ""
      .AddItem "Dang"
      .AddItem "Conk"
    Case "errop"
      .AddItem ""
      .AddItem "Plast"
      .AddItem "teep"
    End Select
  End With

With ThisWorkbook.Worksheets("Tab3").ComboBox1
    Select Case Environ$("USERNAME")
    Case "ron2k_1", "frisbyt", "terryg", "tomcat"
      .AddItem ""
      .AddItem "Frig"
      .AddItem "Tel"
      .AddItem "Dull"
      .AddItem "Bug"
    Case "mguss", "qjang"
      .AddItem ""
      .AddItem "Dang"
      .AddItem "Conk"
    Case "errop"
      .AddItem ""
      .AddItem "Plast"
      .AddItem "teep"
    End Select
  End With
End Sub

The issue is that my list of usernames are pretty extensive, and anytime I need to delete one or add one or add items to a specific username I need to do it to all three comboboxes separately. Is there a condensed (and hopefully non-detrimental in loading time) way to do this.

Thanks again,

 

[shg]

How about ...

    Dim wks         As Worksheet
 
    For Each wks In Worksheets(Array("Tab1", "Tab2", "Tab3"))
        With wks.OLEObjects("ComboBox1").Object
            Select Case Environ$("USERNAME")
                Case "shg"
                    .AddItem ""
                    .AddItem "Frig"
                    .AddItem "Tel"
                    .AddItem "Dull"
                    .AddItem "Bug"
                Case Else
                    .AddItem ""
                    .AddItem "Dang"
                    .AddItem "Conk"
            End Select
        End With
    Next wks

 

[ron2k]

Yep! That works! Thanks shg.

Ron

 

[shg]

You're welcome.

You might also change all those AddItems to

.List =Array("", "Frig", "Tel", "Dull", "Bug")

 

[ron2k]

Even better!!! Thank you very much for your help. My code went from several pages long to probably just half-a-page.

Amazing what you can do when you know...