VBA code to set 2 column Combobox values

Dustan

New Member
Joined
Jun 19, 2012
Messages
47
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.

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:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

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
Back
Top