PDA

View Full Version : [SOLVED] Trying to get a range of cells to show up in my Combo Box



Davespil
06-07-2016, 08:20 AM
Hello, I'm trying to get Part Numbers in Rows A3 through A20 to show up in my Combo Box. When the desired part number from those rows is selected I want that part number to be placed in A23. The problem I'm having is getting the rows to show up in the combo box. Here is the code I have (please note that I have two sheets so I need to specify which sheet I need in the range). The code in bold is where I'm having the problem, the rest of the code works just fine. Thank you in advance.

Here is the Sheet names:
16332


Private Sub PartNumberComboBox_Change()
PartNumberComboBox.RowSource = "Sheet1!Calculations".Range("A3:A20")
Range("A23").Value = PartNumberComboBox.Value
Application.Calculate
DoEvents
Me.NomenclatureAnswerLabel.Caption = Range("B23").Value
End Sub

snb
06-07-2016, 08:58 AM
Please use code tags !

Davespil
06-07-2016, 09:02 AM
Please use code tags !
I'm sorry, but I don't know what you mean. What exactly are code tags?

mdmackillop
06-07-2016, 10:35 AM
What exactly are code tags?
Select your code and click the # button above. Now added to your post

mdmackillop
06-07-2016, 10:42 AM
Syntax error

PartNumberComboBox.RowSource = Worksheets("Calculations").Range("A3:A20")

Davespil
06-07-2016, 10:58 AM
Syntax error

PartNumberComboBox.RowSource = Worksheets("Calculations").Range("A3:A20")

I no longer get an error but the ComboBox is still empty. Here is exactly what I used:


Private Sub PartNumberComboBox_Change()
PartNumberComboBox.RowSource = Worksheets("Calculations").Range("A3:A20")
Range("A23").Value = PartNumberComboBox.Value
Application.Calculate
DoEvents
Me.NomenclatureAnswerLabel.Caption = Range("B23").Value
End Sub

mdmackillop
06-07-2016, 11:05 AM
The row source needs to be set before you can change the combo. I don't know how you are doing this. Can you post a sample workbook?

Davespil
06-07-2016, 11:12 AM
Here is the user form:

16336

OK, here is the work book:


Private Sub CalculateCommandButton_Click()
CalculatedSparesAnswerLabel.Caption = Range("I23")
End Sub
Private Sub CalculatedSpares_Click()
End Sub
Private Sub CalculatedSparesAnswer_Click()
End Sub
Private Sub CalculatedSparesAnswerLabel_Click()
End Sub
Private Sub CalculatedSparesLabel_Click()
End Sub
Private Sub ClearFieldsCommandButton_Click()
Range("A23").ClearContents
Range("F23").ClearContents
Range("G23").ClearContents
Range("S23").ClearContents
Unload Me
SparesCalculation.Show
End Sub
Private Sub ConfidenceLevelComboBox_Change()
End Sub
Private Sub Image1_Click()
End Sub

Private Sub NomenclatureAnswerLabel_Click()
End Sub
Private Sub NomenclatureLabel_Click()
End Sub
Private Sub NumberofLRUsTextBox_Change()
Range("F23").Value = NumberofLRUsTextBox.Value
End Sub
Private Sub OperatingHoursTextBox_Change()
Range("G23").Value = OperatingHoursTextBox.Value
End Sub
Private Sub PartNumberComboBox_Change()
PartNumberComboBox.RowSource = Worksheets("Calculations").Range("A3:A20")
Range("A23").Value = PartNumberComboBox.Value
Application.Calculate
DoEvents
Me.NomenclatureAnswerLabel.Caption = Range("B23").Value
End Sub
Private Sub RTATComboBox_Change()
Range("S23").Value = RTATComboBox.Value
End Sub
Private Sub UserForm_Click()
End Sub

mdmackillop
06-07-2016, 11:25 AM
Hi
If you use Go Advanced, you can attach the file.

Davespil
06-07-2016, 11:29 AM
I'm sorry but I can't do that. Is the code not enough?

mdmackillop
06-07-2016, 11:44 AM
Private Sub UserForm_Activate()
ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:="=Calculations!R3C1:R19C1"
PartNumberComboBox.RowSource = "Data"
End Sub


Private Sub PartNumberComboBox_Change()
Range("A23").Value = PartNumberComboBox.Value
Application.Calculate
DoEvents
Me.NomenclatureAnswerLabel.Caption = Range("B23").Value
End Sub

Davespil
06-07-2016, 11:47 AM
Thank you for all of your help, I put "Calculations!A3:A20" in the RowSource properties for PartNumberComboBox and it worked.