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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
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!
 
Upvote 0
For the numbers use
VBA Code:
 .Range("B2").Value = IIf(Me.ComboBox1 <> "", Val(Me.ComboBox1), "?")
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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