Text from combobox into a spredsheet

Erving

New Member
Joined
Mar 10, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Hi everyone!
Question from a rookie.

I am trying to build a userform wich includes 3 comboboxes. The comboboxes refers to a spredsheet list and 3 columns. So RowSourse = Lists!A2:A10 (Lists is the name of the spredsheet) Also have Lists!B2:B10 and C2:C10.
A2:A10 refers to numbers 1 to 9.
B2:B10 refers to letters a to i.
C2:C10 is a mix of both numbers and letters.

I'd like the chosen value (number or letter) in a combobox to end up in a spredsheet named Front.
I don´t have a problem doing this with numbers. Works perfect.
But letters don't end up in the spredsheet. It's like the combobox can't hold text.
I have tried to use "text" instead of "value" in the code but it does not work.

I run this userform via a macro:

Sub test()

UserForm1.Show

End Sub


UserForm code:

Private Sub CommandButton1_Click()


Dim Numbers As Integer
Dim Letters As String
Dim Mix As Variant

If ComboBox1 = True Then

Numbers = ComboBox1.Value

Else: ComboBox1 = False
Numbers = "?"

End If


If ComboBox2 = True Then

Letters = ComboBox2.Value

Else: ComboBox2 = False
Letters = "?"

End If


If ComboBox3 = True Then

Mix = ComboBox3.Value

Else: ComboBox3 = False
Mix = "?"

End If


Sheets("Front").Range("B2").Value = Numbers
Sheets("Front").Range("C2").Value = Letters
Sheets("Front").Range("D2").Value = Mix
Sheets("Front").Range("F2").Value = Numbers & " / " & Letters & " / " & Mix


Unload UserForm1
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
Try it like
VBA Code:
If ComboBox1 <> "" Then

Numbers = ComboBox1.Value

Else
Numbers = "?"

End If


If ComboBox2 <> "" Then

Letters = ComboBox2.Value

Else
Letters = "?"

End If


If ComboBox3 <> "" Then

Mix = ComboBox3.Value

Else
Mix = "?"

End If
Or slightly simpler
VBA Code:
Private Sub CommandButton1_Click()

With Sheets("Front")
   .Range("B2").Value = IIf(Me.ComboBox1 <> "", Me.ComboBox1, "?")
   .Range("C2").Value = IIf(Me.ComboBox2 <> "", Me.ComboBox2, "?")
   .Range("D2").Value = IIf(Me.ComboBox3 <> "", Me.ComboBox3, "?")
   .Range("F2").Value = .Range("B2") & " / " & .Range("C2") & " / " & Range("D2")
End With

Unload Me
End Sub
 

Erving

New Member
Joined
Mar 10, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Both works.
Thank you so much!
Second solution formated the number into text in the spreadsheet though.
Mayby thats easy to fix?
Anyhow thanx again!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
For the numbers use
VBA Code:
 .Range("B2").Value = IIf(Me.ComboBox1 <> "", Val(Me.ComboBox1), "?")
 

Erving

New Member
Joined
Mar 10, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web

ADVERTISEMENT

Works fine!
Interesting with ComboBox3.
Can hold both numbers and letters.
So the formating can be text or numbers.
Works fine with the first solution though.
Thanx!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
With Sheets("Front")
   .Range("B2").Value = IIf(Me.ComboBox1 <> "", Val(Me.ComboBox1), "?")
   .Range("C2").Value = IIf(Me.ComboBox2 <> "", Me.ComboBox2, "?")
   With .Range("D2")
      Select Case True
         Case Me.ComboBox3 = ""
           .Value = "?"
         Case IsNumeric(Me.ComboBox3)
            .Value = Val(Me.ComboBox3)
         Case Else
            .Value = Me.ComboBox3
      End Select
   End With
   .Range("F2").Value = .Range("B2") & " / " & .Range("C2") & " / " & Range("D2")
End With
 

Erving

New Member
Joined
Mar 10, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
That is beautiful!
Thank you!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,549
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,127,588
Messages
5,625,668
Members
416,124
Latest member
DeMoNloK

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