Combobox question

litestream

Active Member
Joined
Jul 24, 2006
Messages
323
I have 2 Comboboxes on a User Form. Both show the same information taken from the result of a formula in a worksheet cell. For demonstration purposes, both boxes show integers from 1 to 44.

Would it be possible to show the number 1 in the first box and 44 in the second when the form is loaded rather than just 2 blank boxes?

Code:
Private Sub UserForm_Initialize()
Label1.Caption = Sheets("Summary").Range("B16").Value
Label2.Caption = Sheets("Summary").Range("D16").Value

Dim i As Integer
Dim j As Integer

With Sheets("Summary")
With ComboBox3
       For i = 1 To Worksheets("Summary").Range("L19").Value
          .AddItem i
       Next
End With
End With

With Sheets("Summary")
With ComboBox4
       For j = 1 To Worksheets("Summary").Range("L19").Value
          .AddItem j
       Next
End With
End With

End Sub

When the form is loaded, nothing is shown in either Combobox until the arrow is clicked.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

SlinkRN

Well-known Member
Joined
Oct 29, 2002
Messages
701
Hi Litestream,
Go to the properties of the comboboxes and type the appropriate number in the "text". HTH, Slink
 

litestream

Active Member
Joined
Jul 24, 2006
Messages
323
Thanks for your reply.

Changing the "text" property would work fine for showing 1 in the first combobox, but the second box changes constantly. Is there a way to show the last number in the range each time?
 

SlinkRN

Well-known Member
Joined
Oct 29, 2002
Messages
701
Hi again,
Yes, I'm sure that can be done. I assume the value in L19 is the highest number in the combobox so if you assign the value of L19 to a variable and then use code to set the text to that variable that should work. I have to go to work now but if nobody else helps you before tonight I'll take a look at it then. Slink
 

litestream

Active Member
Joined
Jul 24, 2006
Messages
323

ADVERTISEMENT

Thanks, that would be awesome.
 

SlinkRN

Well-known Member
Joined
Oct 29, 2002
Messages
701
OK, here you go :)

Code:
Private Sub UserForm_Initialize()
Label1.Caption = Sheets("Summary").Range("B16").Value
Label2.Caption = Sheets("Summary").Range("D16").Value

Dim i As Integer
Dim j As Integer
Dim txt As Integer

With Sheets("Summary")
With ComboBox3
       For i = 1 To Worksheets("Summary").Range("L19").Value
          .AddItem i
       Next
End With
End With

With Sheets("Summary")
With ComboBox4
       For j = 1 To Worksheets("Summary").Range("L19").Value
          .AddItem j
       Next
    txt = Worksheets("summary").Range("l19").Value
    ComboBox4.Text = txt
        
End With
End With

End Sub

It worked for me, hope it works for you! Slink

PS: You really don't have to assign the value to a variable if you don't want to. You can use this code instead.

Code:
Private Sub UserForm_Initialize()
Label1.Caption = Sheets("Summary").Range("B16").Value
Label2.Caption = Sheets("Summary").Range("D16").Value

Dim i As Integer
Dim j As Integer

With Sheets("Summary")
With ComboBox3
       For i = 1 To Worksheets("Summary").Range("L19").Value
          .AddItem i
       Next
End With
End With

With Sheets("Summary")
With ComboBox4
       For j = 1 To Worksheets("Summary").Range("L19").Value
          .AddItem j
       Next
     ComboBox4.Text = Worksheets("summary").Range("l19").Value
        
End With
End With

End Sub
 

Forum statistics

Threads
1,136,266
Messages
5,674,722
Members
419,521
Latest member
Jasonnie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top