I'm abbreviating my descriptions to paint the picture.
I have a userform that has 2 comboboxes on it.
cmbPartNumber and cmbAccount
cmbAccount is 2 columns populated from a worksheet
For each row there is always a value in column 0 and sometimes a value in column 1
Click save on the userform and it spits the cmbPartNumber to Range("A1") and the 2 values from cmbAccount into one cell on a worksheet in one of 2 formats
If both columns have a value,
cmbAccountColumn0Value:cmbAccountColumn1Value
If only the first column has a value
cmbAccountColumn1Value
Userform refresh and cmbPartNumber is populated with all existing part numbers in the spreadsheet
Change cmbPartNumber to an existing value in the cmbPartNumber list and cmbAccount values get set by the offset column of the partnumber - the same as was populated above.
I then use InStr to determine if the Account cell is the 2 part format or the single part format (by presence or lack there of of the ":")
Then use Split(account cell string, ":")(0) to get the string value left of the ":" and (1) to get the value right of the ":"
All of the above works good.
I then want to make the 2 column values of the cmbAccount combobox = the 2 split variables (we will call them mainAccount(left) and subAccount(right)
With the 2 part account format - all of the above works perfect. The cmbAccount combobox values are set to column(0) = mainAccount and column(1) = subAccount
The issue i'm running into is...... if the account cell format is only 1 part (ie no ":" or right value) then the subAccount value = ""
I can't figure out how to set the cmbAccount combobox values to column(0) = mainAccount and column(1) = "" - even though this combobox list contains a row where it should exist.
Here is my code with the non relevant stuff cut out.
cmbAccount1 is populated in the Userform Initialize - based on the values in the Account column of the worksheet
Row 1 has a mainAccount and a subAccount formatted as mainAccount:subAccount
Row 2 has a mainAccount only formatted as mainAccount
Both the row items that i am trying to select exist in the list but I can only get the first one to select. I can't get the second one to select if there is no subAccount.
Please - any help is appreciated.
I have a userform that has 2 comboboxes on it.
cmbPartNumber and cmbAccount
cmbAccount is 2 columns populated from a worksheet
For each row there is always a value in column 0 and sometimes a value in column 1
Click save on the userform and it spits the cmbPartNumber to Range("A1") and the 2 values from cmbAccount into one cell on a worksheet in one of 2 formats
If both columns have a value,
cmbAccountColumn0Value:cmbAccountColumn1Value
If only the first column has a value
cmbAccountColumn1Value
Userform refresh and cmbPartNumber is populated with all existing part numbers in the spreadsheet
Change cmbPartNumber to an existing value in the cmbPartNumber list and cmbAccount values get set by the offset column of the partnumber - the same as was populated above.
I then use InStr to determine if the Account cell is the 2 part format or the single part format (by presence or lack there of of the ":")
Then use Split(account cell string, ":")(0) to get the string value left of the ":" and (1) to get the value right of the ":"
All of the above works good.
I then want to make the 2 column values of the cmbAccount combobox = the 2 split variables (we will call them mainAccount(left) and subAccount(right)
With the 2 part account format - all of the above works perfect. The cmbAccount combobox values are set to column(0) = mainAccount and column(1) = subAccount
The issue i'm running into is...... if the account cell format is only 1 part (ie no ":" or right value) then the subAccount value = ""
I can't figure out how to set the cmbAccount combobox values to column(0) = mainAccount and column(1) = "" - even though this combobox list contains a row where it should exist.
Here is my code with the non relevant stuff cut out.
Code:
Private Sub cmbPartNumber1_Change()If cmbPartNumber1.Text = "" Or cmbPartNumber1.Text = "Part Number:" Then GoTo EndSubRoutine
Dim wsSupplierItemInquiry As Worksheet
Dim rngPartNumber As Range
Dim strPartNumber As String, strPartName As String, strAccount As String, strMainAccount As String, strSubAccount As String
Dim lngPartNumberRow As Long
Set wsSupplierItemInquiry = Worksheets("Supplier Item Inquiry")
strPartNumber = NewPurchaseOrder.cmbPartNumber1.Value
Set rngPartNumber = wsSupplierItemInquiry.Range("B:B").Find(what:=strPartNumber, LookIn:=xlFormulas, lookat:=xlWhole)
If Not (rngPartNumber Is Nothing) Then
lngPartNumberRow = rngPartNumber.Row
strPartName = wsSupplierItemInquiry.Range("C" & lngPartNumberRow).Value
strAccount = wsSupplierItemInquiry.Range("E" & lngPartNumberRow).Value
If InStr(strAccount, ":") <> 0 Then
strMainAccount = Split(strAccount, ":")(0)
strSubAccount = Split(strAccount, ":")(1)
Else
strMainAccount = strAccount
strSubAccount = ""
End If
With txtPartName1
.Text = strPartName
End With
With cmbAccount1
.Text = strSubAccount
.Value = strMainAccount
End With
Else
With txtPartName1
.Text = "Part Name:"
End With
With cmbAccount1
.Text = "Account:"
End With
End If
EndSubRoutine:
End Sub
cmbAccount1 is populated in the Userform Initialize - based on the values in the Account column of the worksheet
Row 1 has a mainAccount and a subAccount formatted as mainAccount:subAccount
Row 2 has a mainAccount only formatted as mainAccount
Both the row items that i am trying to select exist in the list but I can only get the first one to select. I can't get the second one to select if there is no subAccount.
Please - any help is appreciated.
Last edited: