Solved: .text to .value

[Learner123]

Hi all,

Is there a way to transfer a ComboBox.text into a ComboBox.value?

I have a range of cells in a worksheet that become autonumbered when cetain cells are populated (PO-1, PO-2, etc.). A comboBox then pulls the autonumber list and reflects them as text. I then call a procedure that looks up the selected comboBox text, but it can't find the value on the sheet since the sheet reflects the comboBox as a ".text" and not a ".value".

Does anyone know how to correct this problem?

Thank you in advance for your time!

[mdmackillop]

You can use CINT(combobox1) or other conversion to force text to a number.

[Learner123]

Mdmackillop, thank you for the feedback!

Not sure how to apply the CINT in the below example. Any suggestions?

I am trying to develop code that I can insert prior to the below code, so that the conversion from ComboBox2.text to ComboBox2.value allows for the below to function correctly.

Set c = Columns(1).Find(ComboBox2.Value, lookat:=xlWhole)

[mdmackillop]

Set c = Columns(1).Find(Cint(ComboBox2.Value), lookat:=xlWhole)

[Learner123]

Thanks again for the reply.

I attempted to adjust the code as mentioned above but recieved an error (Run-time error '13': Type mismatch).

In the attempt to better express my problem, I have attached a sample of the workbook. If possible, can you show how to correct the error.

My specific problem exists when a user clicks on the 'CO Setup' command. After the userform is filled in (select 'General Contractor' in 1st comboBox for this problem), the userform searches for the last comboBox value but cannot find it.

I am very thankful for your time and appriciate any help!

[Bob Phillips]

Searches for what last Combobox value?

[Learner123]

ComboBox2 in Userform3.

[Bob Phillips]

There is nothing in there, hence my question.

[Learner123]

Oh...ComboBox1 will need to be chosen first. If you chose 'General Contractor' in ComboBox1 then you different options appear in ComboBox2.

Thanks for your help!!

[Bob Phillips]

Yeah I got that and Combobox2 does get populated but it doesn't automatically show any value. What exactly do you want it to do?

[Learner123]

After filling in Userform3, and clicking update, I am trying to insert a row below the specified comboBox2 value (ex: "PO-1"). However, after clicking on 'Update' the code is not able to find "PO-1".

I think this is because the sheet reflects the '"PO-1" as a value and comboBox2 reflects "PO-1" as text.

Any ideas on how to correct this?

I tried using CInt as per Mdmackillop suggestion, but it gives me an error.

[Bob Phillips]

I see now.

You are right about Text and Value, bvut you didn't tell us that the PO# is a custom format display :).

Anyway, all you need to do is load the Value property into the combobox, or assuming you want to see PO-#, then you need to strip it from the lookup value.

I have also tidied the code for you, to remove all of the repetition



Private Sub Cancel1_Click()
Unload Me
End Sub

Private Sub ComboBox1_Change()
Dim ws As Worksheet

Select Case ComboBox1.Value

Case "General Contractor": Call ActionSelection("GC", "General Contractor")
Case "Data Cabling": Call ActionSelection("DC", "Data Cabling")
Case "Flooring & Wallcovering": Call ActionSelection("FW", "Flooring & Wallcovering")
Case "Legal": Call ActionSelection("Legal", "Legal")
Case "Architectural": Call ActionSelection("Architectural", "Architectural")
Case "M&E Engineering": Call ActionSelection("M&E", "M&E Engineering")
Case "Project Management": Call ActionSelection("PM", "Project Management")
Case "Furniture": Call ActionSelection("Furniture", "Furniture")
Case "Security": Call ActionSelection("Security", "Security")
Case "Audio Visual": Call ActionSelection("AV", "Audio Visual")
Case "Signage": Call ActionSelection("Signage", "Signage")
Case "Churn": Call ActionSelection("Churn", "Churn")
Case "ITI": Call ActionSelection("ITI", "ITI")
Case "Misc": Call ActionSelection("Misc", "Misc")
End Select

End Sub


Private Sub Update_Click()
Dim RowCount As Long
Dim c As Range

If Me.ComboBox1.Value = "" Then
MsgBox "Please specify which category you would like to update.", vbExclamation, "NOT SURE WHY"
Me.ComboBox1.SetFocus
Exit Sub
End If

If Me.TextBox1a.Value = "" Then
MsgBox "Please specify vendor.", vbExclamation, "NOT SURE WHY"
Me.TextBox1a.SetFocus
Exit Sub
End If

If Me.TextBox2a.Value = "" Then
MsgBox "Please specify change order dollar amount.", vbExclamation, "NOT SURE WHY"
Me.TextBox2a.SetFocus
Exit Sub
End If

If Me.TextBox3a.Value = "" Then
MsgBox "Please enter today's date.", vbExclamation, "NOT SURE WHY"
Me.TextBox3a.SetFocus
Exit Sub
End If

If Me.ComboBox2.Value = "" Then
MsgBox "Please PO in which change order should be applied to.", vbExclamation, "NOT SURE WHY"
Me.ComboBox2.SetFocus
Exit Sub
End If

''''''''''''''''''''''''''''' Figure the below out


Set c = Columns(1).Find(Replace(ComboBox2.Value, "PO-", ""), lookat:=xlWhole)

If Not c Is Nothing Then
Set c = c.Offset(1, 1)
c.Offset(0, 0).EntireRow.Insert
c.Offset(-1, 1) = Me.TextBox1a
c.Offset(-1, 4) = Me.TextBox2a
c.Offset(-1, 7) = Me.TextBox3a

Else
MsgBox "Search item not found!", vbCritical
Exit Sub
End If

End Sub

Private Function ActionSelection(ByVal id As String, ByVal msg As String)

Worksheets(id).Visible = True
Application.Goto ActiveWorkbook.Sheets("GC").Range("A1")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> id And ws.Name <> "Overview" Then
ws.Visible = False
End If
Next ws

If Range("A8") = "" Then
Unload Me
MsgBox "There are no POs under " & msg, vbCritical
Else
For Each cell In Range("Test").SpecialCells(xlConstants)
ComboBox2.AddItem cell.Text
Next cell
End If
End Function

[Learner123]

Wow! I am very impressed!! Can't believe how fast you rejiggered everything.

Thanks a bunch!

[Learner123]

Hi again - I made an addition to my autonumber code and it is messing with the below code :banghead: :banghead: :banghead: :

Set c = Columns(1).Find(Replace(ComboBox2.Value, "PO-", ""), lookat:=xlWhole)

What I did was change the autonumber code on the sheet and now the above code is not working.

Can someone please help with the above code??

I have attached an updated sample.

Thank you very much for any help!!!!

[Bob Phillips]

Private Sub Update_Click()
Dim RowCount As Long
Dim c As Range

If Me.ComboBox1.Value = "" Then
MsgBox "Please specify which category you would like to update.", vbExclamation, "NOT SURE WHY"
Me.ComboBox1.SetFocus
Exit Sub
End If

If Me.TextBox1a.Value = "" Then
MsgBox "Please specify vendor.", vbExclamation, "NOT SURE WHY"
Me.TextBox1a.SetFocus
Exit Sub
End If

If Me.TextBox2a.Value = "" Then
MsgBox "Please specify change order dollar amount.", vbExclamation, "NOT SURE WHY"
Me.TextBox2a.SetFocus
Exit Sub
End If

If Me.TextBox3a.Value = "" Then
MsgBox "Please enter today's date.", vbExclamation, "NOT SURE WHY"
Me.TextBox3a.SetFocus
Exit Sub
End If

If Me.ComboBox2.Value = "" Then
MsgBox "Please PO in which change order should be applied to.", vbExclamation, "NOT SURE WHY"
Me.ComboBox2.SetFocus
Exit Sub
End If

''''''''''''''''''''''''''''' Figure the below out


Set c = Columns(1).Find(Val(Replace(ComboBox2.Value, "PO-100662-", "")), lookat:=xlWhole)

If Not c Is Nothing Then
Set c = c.Offset(1, 1)
c.Offset(0, 0).EntireRow.Insert
c.Offset(-1, 1) = Me.TextBox1a
c.Offset(-1, 4) = Me.TextBox2a.Value
c.Offset(-1, 7) = Me.TextBox3a

Else
MsgBox "Search item not found!", vbCritical
Exit Sub
End If

End Sub

[Learner123]

Once again...VBA EXPRESS to the rescue.

THANK YOU!

[Learner123]

Hi again,

I am having a very difficult time wrapping up this project. As I try to tie up loose ends, I run into small issues that give problems. Any direction with the below problem is greatly appreciated!

I have set up an autonumbering system that follow the below sequence:

PO-100662-01
PO-100662-02
PO-100662-03
etc.

I now realize that I have to include additional info within the autonumber, but am having issues getting it to work.

I would like the tab name to be part of the PO# that is produced. For instance, if someone creates a PO under General Construction, I would like to have the PO# to look like the below:

PO-GC-100662-01
PO-GC-100662-02
PO-GC-100662-03

And if someone wanted to create a PO# under Data Cabling, the PO# would look like the below:

PO-DC-100662-01
PO-DC-100662-02
PO-DC-100662-03

I truly appreciate your time and help!:bow:

Thanks in advance!!!

[Bob Phillips]

Is this what you mean?



Const FormatPO As String = """PO-<tab>-100662-""000"
Private tabID As String

Private Sub Cancel2_Click()
Application.Goto ActiveWorkbook.Sheets("Overview").Range("A1")
Unload Me
End Sub


Private Sub UserForm_Initialize()
Dim Rng As Range
Dim i As Long
Me.ComboBox1.RowSource = ""
Set Rng = Range("Vendors")
For i = 1 To Rng.Rows.Count
If Rng(i) <> "" Then
Me.ComboBox1.AddItem Rng(i)
End If
Next i
End Sub

Private Sub ComboBox3_Change()
Dim tabName As String

Select Case ComboBox3.Value

Case "General Contractor": tabName = "GC": tabID = "GC"
Case "Flooring & Wallcovering": tabName = "FW": tabID = "FW"
Case "Data Cabling": tabName = "DC": tabID = "DC"
Case "Legal": tabName = "Legal": tabID = "LG"
Case "Architectural": tabName = "Architectural": tabID = "AR"
Case "M&E Engineering": tabName = "M&E": tabID = "ME"
Case "Project Management": tabName = "PM": tabID = "PM"
Case "Furniture": tabName = "Furniture": tabID = "FT"
Case "Security": tabName = "Security": tabID = "SC"
Case "Audio Visual": tabName = "AV": tabID = "AV"
Case "Signage": tabName = "Audio Visual": tabID = "SG"
Case "Churn": tabName = "Churn": tabID = "CH"
Case "ITI": tabName = "ITI": tabID = "IT"
Case "Misc": tabName = "Misc": tabID = "MS"
Case "ITI": tabName = "ITI": tabID = "IT"
Case "ITI": tabName = "ITI": tabID = "IT"
End Select

Call SetVisibility(tabID)
End Sub

Private Function SetVisibility(ByVal tabName As String)
Dim ws As Worksheet

Worksheets(tabName).Visible = True
Application.Goto ActiveWorkbook.Sheets(tabName).Range("A1")
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> tabName And ws.Name <> "Overview" Then
ws.Visible = False
End If
Next ws

End Function

Private Sub Apply_Click()
Dim RowCount As Long
Dim c As Range

If Me.ComboBox3.Value = "" Then
MsgBox "Please specify which category you would like to update.", vbExclamation, "NOT SURE WHY"
Me.ComboBox1.SetFocus
Exit Sub
End If

If Me.ComboBox1.Value = "" Then
MsgBox "Please specify vendor.", vbExclamation, "NOT SURE WHY"
Me.ComboBox1.SetFocus
Exit Sub
End If

If Me.TextBox1.Value = "" Then
MsgBox "Please enter discription.", vbExclamation, "NOT SURE WHY"
Me.TextBox1.SetFocus
Exit Sub
End If


''''''''''''''''''''''''''''''' Figure out the below


Set c = Columns(1).Find("PO #", lookat:=xlWhole)
If c.Offset(1) = "" Then
Set c = c.Offset(1, 2)
Else
Set c = Columns(3).Find("Vendor Name", lookat:=xlWhole)
Set c = c.End(xlDown)(2)
End If

c.Offset() = Me.ComboBox1
c.Offset(, 2) = Me.TextBox2.Value
c.Offset(, 1) = Me.TextBox1
c.Offset(0, -2).NumberFormat = Replace(FormatPO, "<tab>", tabID)
c(2).EntireRow.Insert

End Sub


Private Sub CommandButton1_Click()
Unload Me
Load UserForm6
UserForm6.Show
End Sub

[Learner123]

Thats exactly what I am looking for! :joy:

Although, now there is a problem with the "CO Setup" button. It does not recgonize the new value formats. I know I am pushing my luck, but is it possible to help out with correcting the problem.

I would like for a CO # to be generated when a user clicks on "CO setup" --> "Update" button. The CO # would have the below format:

CO-001
CO-002
etc.

However, if user decides to produce a CO under another PO#, the CO# would reset and start from CO-001 and move sequentially once another update is made.

I plan on posting the completed workbook for public use. VBA Express has been very involved in its development and would like to share it with anyone interested.

Also, I would like to make a donation to the site as it has proven to be a great channel for learning!

Thank you again very much for all your time!!

[Bob Phillips]

Try this