COMBOBOX AND INPUT BOX

granty

Board Regular
Joined
Jul 28, 2005
Messages
117
Hi,

I have three items on my combo box. I want to write a code, where users are prompted to enter a comment in an input box depending on the item they select from the list. I then wish to save these comments on an excel spreadsheet.

can anyone help
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
granty,

this looks like a "Select Case"
to be sure:
can you provide some more details ?

kind regards,
Erik
 

granty

Board Regular
Joined
Jul 28, 2005
Messages
117
I think it may be.

I want user to select one item from 3 in a combo box. if they choose an item, I want and input box to appear so they can enter a comment based on the item they have chosen. They must choose an item from the list and they must comment on their choice in an input box. I then want to save these comment on an excel spreadsheet.

Can you help??
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
your first post said
enter a comment in an input box depending on the item they select from the list
DEPENDING was the word which took my attention, therefore my question for some more info

in your second post this isn't mentioned
let's see if this would help
assuming you have a combobox1 on your sheet

Code:
Option Explicit

Private Sub ComboBox1_Click()
Dim R As Long
Dim Answer As Variant 'MUST BE VARIANT ! when String checking for False errors

R = Cells(Rows.Count, 1).End(xlUp).Row + 1

    Do
    Answer = ""
    Answer = Application.InputBox("Please comment your current choise: """ & ComboBox1 & """", "TITLE", "")
      If Answer = "" Then
        If MsgBox("No answer." & Chr(10) & _
        "Do you want to continue?", 52, "No entry") = vbNo Then GoTo skip
      ElseIf Answer = False Then GoTo skip
      End If
    Loop While Answer = ""

Cells(R, 1) = ComboBox1
Cells(R, 2) = Answer
skip:
ComboBox1 = ""
End Sub
instead of using the click event which is rather "direct" you could use a button to confirm the choise

best regards,
Erik
 

granty

Board Regular
Joined
Jul 28, 2005
Messages
117

ADVERTISEMENT

Thank you for your reply, erik.

What I meant by 'depending on the item select' is that if they select A, for instant, an input box will come up and ask them a question relating only to A. If B is chosen, another prompt as them to comment on information specific to B etc

E.g if 'school' is chosen..... prompt comes up "Please provide information of qualification gained"

If 'work' is chosen ...... prompt comer up " Please provide information of recent skills gained" etc.

Thank you

thank you for your help.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

also try this
Code:
Option Explicit

Private Sub ComboBox1_Click()
'Erik Van Geit
'051124

Dim R As Long
Dim msg As String
Dim Answer As Variant 

R = Cells(Rows.Count, 1).End(xlUp).Row + 1
Const txt As String = "Please provide information of "

Select Case ComboBox1
Case "school": msg = txt & "qualification gained"
Case "work":   msg = txt & "recent skills gained"
Case "street", "house", "wood": msg = txt & "this curious choice"
Case Else: Exit Sub
End Select

    Do
    Answer = ""
    Answer = Application.InputBox(msg, "TITLE", "")
      If Answer = "" Then
        If MsgBox("No answer." & Chr(10) & _
        "Do you want to continue?", 52, "No entry") = vbNo Then GoTo skip
      ElseIf Answer = False Then GoTo skip
      End If
    Loop While Answer = ""

Cells(R, 1) = ComboBox1
Cells(R, 2) = Answer
skip:
ComboBox1 = ""
End Sub
 

granty

Board Regular
Joined
Jul 28, 2005
Messages
117
thank you.

I have more questions.

I already have these codes

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtdate.Value
ws.Cells(iRow, 2).Value = Me.txtName.Value
ws.Cells(iRow, 3).Value = Me.cboStatus.Value

Please could you tell me how I can incorporate your code below with the above as I would like the comments from the input box to go to an excel spreadsheet

R = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(R, 1) = ComboBox1
Cells(R, 2) = Answer

Many thanks
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
you were talking about a combobox + inputbox
you received code for that
I asked you if it is working, since you didn't give feedback

before going further tell me
does it work ?

I'm not talking about the fact if it is doming exactly what you want, but just does it do something ?
you may need to adapt "combobox1" to the name of your combobox
 

granty

Board Regular
Joined
Jul 28, 2005
Messages
117
the code works well. thank you.

I now want to update the spreadsheet withtinformation entered in the input box. I have written codes to update the spreadsheet with data from combox box, but not from the input box. (per my prev posting).

any ideas?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,324
Messages
5,571,551
Members
412,403
Latest member
Iggvsbsb
Top