Add a combo list to Input Box?

M

mholt2110

New Member
Joined
Aug 18, 2009
Messages
28
Hi is it possible to add a combo list to an input box macro

Im using the following macro, but Id like column C to have the input options of; Invoice Payment, Refund, Personal Deposit or Sale of Assets. instead of a generic option to write text.

Would I do this with a combo box or list built into the iput box or another way maybe? any help would be appreciated.

ps 3rd Range set down is the bit that needs changing, and will cause an error as it is.


HTML: 
Sub AddIncomingPayment()

    Range("A2").End(xlDown).Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = InputBox("Date of Incoming Payment")
    
    Range("B2").End(xlDown).Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = InputBox("Value of Incoming Payment")

   Range("C2").End(xlDown).Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = ComboBox1.AddItem Cells(I, "Z")
       
    Range("D2").End(xlDown).Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = InputBox("Invoice Reference")

Also, my database is made up of 6 sheets, which help to create and log invoices, customer details and expenses details.

I intend to make it as automatic as possible, nut am unsure how to change between sheets when writing the macros?

I have tables on two sheets which are filled in with parts of the same series of information ie. Invoice number, date and value are on both, and customer name and address is on one. If possible I'd like to use one macro to do this rather than the two I currently use. so say I want to have input boxes in Sheet 1 columns A through D as per above and then similarly Sheet 2 Columns A, B, E, F & H, how do I do this.


Many thanks for any help that can be given.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Hello,

Welcome to the message board!

It sound like you could use a userform with a listbox.

Press Alt+F11 to open the VBEditor

Use the keystrokes Alt - I - U to inset a userform.

From the toolbox select a listbox and draw on the userform to create

Now you will need to set the properties of the listbox.

If you do not see the properties window on the left press F4

Liststyle should be fmliststyle option
Rowsource should be your "list" in the worksheet like: Sheet1!Z1:Z4

where range z1:z4 contains <TABLE style="WIDTH: 88pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=117><COLGROUP><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 88pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2920929 class=xl63 height=20 width=117>Invoice Payment</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>Refund

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2920929 class=xl64 height=20>Personal Deposit

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2920929 class=xl64 height=20>Sale of Assets.

</TD></TR></TBODY></TABLE>

Now right click the listbox a choose veiw code.

paste in this:

Code: 
Private Sub ListBox1_Change()
Range("C2").End(xlDown).Offset(1, 0).Value = ListBox1.Value
End Sub

close that window

Now right click somewhere on the userform itself.

Paste in this:

Code: 
Private Sub UserForm_Terminate()
Unload Me
End Sub

then use this in the standard module.

Code: 
Sub b()
    Range("A2").End(xlDown).Offset(1, 0).Value = InputBox("Date of Incoming Payment")
    Range("B2").End(xlDown).Offset(1, 0).Value = InputBox("Value of Incoming Payment")
UserForm1.Show
    
    Range("D2").End(xlDown).Offset(1, 0).Value = InputBox("Invoice Reference")
End Sub

I hope this hasn't been confusing...



To refer to different sheets try like:

sheets(1).range("a1")
or
sheets("Invoices").range...
 
Upvote 0
Hi Repairman,

Thank you so much for taking the time to reply,
That all made very good sense, I use a mac, and I was able to follow most of it without any problems, It also helped me to understand a bit more about VB.

However Im Sorry for my late reply, I did come across a stumbling block, as it seems excel on the mac does not use rowsource, I have spent a few days trying to find a work around on the forums, I found one which covers the combo box but Im not sure if it will do the job fully. maybe you will be able to advise.

I was also not aware of the User form method and have created a user form with all the inputs Id like to use on one form, instead of the old method with just one text box per form.


There are 7 input boxes (6 Text boxes - 1 Combo Box) & two Command buttons 'Cancel' and 'Add Outgoing Payment'

I have put the code that I have so far below
Also a general idea of what Id like the two buttons to do if possible.
Ive managed to do Cancel as I found this fairly easily on the forum.

If you could help me with the following 4 Im hoping that if its straight forward Ill be able to work out how to do the others from these.

ValueOutgoing1_change()
UserForm_Initialize() [This is a combo box which the code seems ok]
UserForm_Click() [This was on the code already, Will I need it?]
CmdAdd_Click()



HTML: 
Private Sub DateOutgoing1_Change()
 ' TextBox - When CmdAdd Button is clicked Input data needs to be Transferred onto sheet"Income - Outgoings" in the first free cell in a table in column "A" which starts on row 2.
End Sub


Private Sub ValueOutgoing1_Change()
 ' TextBox - When CmdAdd Button is clicked Input data needs to be Transferred onto sheet"Income - Outgoings" in the first free cell in a table in column "B" which starts on row 2.
End Sub

Private Sub OutgoingType1_Change()
' Combo Box - See Next step as this one may not be needed if below works on its own
End Sub

            Private Sub UserForm_Initialize()
                OutgoingType1.List = Worksheets("MacroData").Range("B2:B5").Value
                ' Combo Box - When CmdAdd Button is clicked choosen option to be taken from Sheet"MacroData" Range (B2:B5) from a table.
                ' Is there a way I can add to the table at a later date, and this be added to the code automatically?
                ' ie change above range to (B2:B7)?
                ' The above code seems to work but will the CmdAdd button add them to the database?
                ' When CmdAdd Button is clicked Input data needs to be Transferred onto sheet"Income - Outgoings" in the first free cell in a table in column "F" which starts on row 2.
            End Sub

Private Sub OutgoingDescription1_Change()
' TextBox - When CmdAdd Button is clicked Input data needs to be Transferred onto sheet"Income - Outgoings" in the first free cell in a table in column "G" which starts on row 2.
End Sub

Private Sub WhereFrom1_Change()
 ' TextBox - When CmdAdd Button is clicked Input data needs to be Transferred onto sheet"Income - Outgoings" in the first free cell in a table in column "H" which starts on row 2.
End Sub

Private Sub ForWhat1_Change()
' TextBox - When CmdAdd Button is clicked Input data needs to be Transferred onto sheet"Income - Outgoings" in the first free cell in a table in column "I" which starts on row 2.
End Sub

Private Sub ForWho1_Change()
' TextBox - When CmdAdd Button is clicked Input data needs to be Transferred onto sheet"Income - Outgoings" in the first free cell in a table in column "J" which starts on row 2.
End Sub

Private Sub UserForm_Click()
'do I need this one?
End Sub

Private Sub CmdAdd_Click()
  'This button is to add all the above data to the database, also if one of the above 6 text boxes or the combo box is left blank I would like to insert a "  '  " to ensure that that cell is classed as used but is still blank.
End Sub
            
            Private Sub CmdCancel_Click()
                    ' Close the UserForm. Ps I Think I got this one Right =D
                   Unload Me
            End Sub



Also I tried the Sheets("MacroData").Range("B2") you mentioned but it didn't seem to work, The button is on a sheet called 'Quotation' and some of the data is for the "Customer Details" Sheet and some for the "Macro Data" sheet
Am I doing something wrong?


HTML: 
Sub AddIncomingPayment()

    Sheets("Customer Details").Range("A2").End(xlDown).Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = InputBox("Date of Incoming Payment")
    
    Sheets("MacroData").Range("B2").End(xlDown).Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = InputBox("Value of Incoming Payment")


Thanks again for any help you can and have given me, Im always learning on VB and Excel and you've helped me lots already.

I can see many a night sitting trying to get more out of the UserForm editor. much easier than what I had done previously.

Thanks again
Martin.
 
Upvote 0
Hello Martin,

Question 3, Userform Click is like a default that automatically gets generated. Feel free to delete.

I will have a look at the other questions soon...

I do not use a mac. I suppose you are using Excel 2011?
 
Upvote 0
Is the OutgoingType1.list working to populate the listbox?

If so then Yes, it is possible to dynamically update the listbox from the range in B2:B?

I think You will find if the below code works for you, adapting the code for each may be easy.


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> DateOutgoing1_Change()<br><SPAN style="color:#007F00">'''Below is 2 Examples.</SPAN><br><br><br><SPAN style="color:#007F00">'''Change Table1 to your Table Name, B to your Column B Header</SPAN><br><br><SPAN style="color:#007F00">'    With Range("Table1")</SPAN><br><SPAN style="color:#007F00">'        .Item(.Count).Offset(1, 0).Select</SPAN><br><SPAN style="color:#007F00">'    End With</SPAN><br><br>Sheets("Income - Outgoings").Range("A" & Range("A" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = _<br>        Textbox1.Value<br><br>    <br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Here would be transfering the Value of a ComboBox, named ComboBox1 to the last row in column F:F within sheet Income - Outgoing.

<font face=Courier New>Sheets("Income - Outgoings").Range("F" & Range("F" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = _<br>        Combobox1.Value</FONT>


Very similar in coding. Just change the names of textboxes and the letters of cell ranges to suit.

Also note the absents of "Select" within the code. Generally, it is not needed and actually slows the process.

Let me know how this does for you and if you have any problems with it.

:)
 
Upvote 0
Dynamically setting the rowsource workaround for Mac?

<font face=Courier New>    <SPAN style="color:#007F00">'''This should work if the no other data was placed into the column (B)</SPAN><br>   OutgoingType1.List = Worksheets("MacroData").Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)<br>   <br>   <SPAN style="color:#007F00">'''Be sure to leave a blank cell beneath the list data</SPAN><br>   OutgoingType1.List = Worksheets("MacroData").Range("B2:B" & Range("B2").End(xlDown).Row)</FONT>
 
Upvote 0
<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CmdAdd_Click()<br>  <SPAN style="color:#007F00">'This button is to add all the above data to the database,</SPAN><br>  <SPAN style="color:#007F00">'also if one of the above 6 text boxes or the combo box is left blank I would like</SPAN><br>  <SPAN style="color:#007F00">'to insert a "  '  " to ensure that that cell is classed as used but is still blank.</SPAN><br>  <br>  <br>    <SPAN style="color:#00007F">If</SPAN> OutgoingDescription1.Value <> "" <SPAN style="color:#00007F">Then</SPAN><br>        Sheets("Income - Outgoings").Range("G" & Range("G" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = _<br>                OutgoingDescription1.Value<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Sheets("Income - Outgoings").Range("G" & Range("G" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = _<br>                " - "<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> WhereFrom1.Value <> "" <SPAN style="color:#00007F">Then</SPAN><br>        Sheets("Income - Outgoings").Range("G" & Range("G" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = _<br>                WhereFrom1.Value<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Sheets("Income - Outgoings").Range("H" & Range("H" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = _<br>                " - "<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

You can delete all of the "private sub wherefrom change" type. Basically all you will need is cmdadd, cancel, and intialize.
 
Last edited:
Upvote 0
Hi Thank you again for your time, Im hoping to get a chance to try this out soon, and I will let you know the outcome,

Ps I am running 2011, & I think the OutgoingType1.list is working to populate the listbox, (I'm guessing that means when doing a test run it populates the list)

Thank you so much for the time youve spent, this will help me so much and hopefully help me understand a bit more too about how to use VB.

I will reply back soon many thanks

Martin
 
Upvote 0
Hi Repairman

I've had a go and got much further, I really appreciate how much you've helped me

I've run into a 4 main snags, which I've detailed below, Hopefully This will be it =D

1.a. Sorry I didnt understand this one below, So I've left It out at the moment as it seemed to cause some Errors and Bugs.
how would it need to be written as some of it was in notes form so I didn't know if I needed to leave it or change it?

1.b. Was this to insert the information into the database table? if so it may fix my 3rd question.

1.c. Or was it to help populate the table for the dynamic List for OutgoingTypes? Which may help with my 2nd Question

Code: 
Sub DateOutgoing1_Change()
                
                ' ''Below is 2 Examples.

                ' ''Change Table1 to your Table Name, B to your Column B Header

'       With Range("Table1[B]")
'       .Item(.Count).Offset(1, 0).Select
'       End With

'       Sheets("Income - Outgoings").Range("A" & Range("A" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value =
'       TextBox1.Value
                
End Sub


2.a. This next one works on its own but the OutgoingType1.List it is not dynamic as the two rows of code you gave me unfortunately didn't work, I may have misunderstood what I needed to do but I was getting errors when I tried to add either of the two lines to that you gave.

2.b. I was hoping in future to have a button on this UserForm to Add an Outgoing Type to the list (Sheet=MacroData, Table Name=OutgoingTypes, Column=Outgoing Types) again hopefully dynamically changing the range used here. I guess it makes sense to tell you this incase it wastes any time.


Code: 
Private Sub UserForm_Initialize()
                
                ' Populates Default Userform Values as " - " or " 1 "
                ' Populates "Outgoing Type" Combo List
        
        DateOutgoing1.Value = ""
        ValueOutgoing1.Value = ""
        OutgoingType1.Value = ""
        OutgoingDescription1.Value = ""
        Quantity1.Value = 1
        WhereFrom1.Value = ""
        ForWhat1.Value = ""
        ForWho1.Value = ""
        
        OutgoingType1.List = Worksheets("MacroData").Range("B2:B5").Value
              
End Sub



3. This code works, but inserts the data in the first unused row below the table rather than in the first unused row in it.
Is there a way round this?


Code: 
Private Sub CmdAdd_Click()
                
                ' Button inserts all data onto the databases,
                ' Inserts " - " If field left blank ensuring cell is classed as used.
                ' Then unloads
     
    If DateOutgoing1.Value <> "" Then
        Sheets("Income - Outgoings").Range("A" & Range("A" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = _
        DateOutgoing1.Value
    Else
        Sheets("Income - Outgoings").Range("A" & Range("A" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = _
            " - "
    End If
    
    
    If ValueOutgoing1.Value <> "" Then
        Sheets("Income - Outgoings").Range("B" & Range("B" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = _
        ValueOutgoing1.Value
    Else
        Sheets("Income - Outgoings").Range("B" & Range("B" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = _
        " - "
    End If
  
  
    If OutgoingType1.Value <> "" Then
        Sheets("Income - Outgoings").Range("F" & Range("F" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = _
        OutgoingType1.Value
    Else
        Sheets("Income - Outgoings").Range("F" & Range("F" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = _
        " - "
    End If
    
 ..........
    
        Unload Me
    
End Sub


4. Finally these two lines didn't seem to work, column B was blank except for the 4 options and header? So I'm not sure If I did it right


Dynamically setting the rowsource workaround for Mac?

'''This should work if the no other data was placed into the column (B)
OutgoingType1.List = Worksheets("MacroData").Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)

'''Be sure to leave a blank cell beneath the list data
OutgoingType1.List = Worksheets("MacroData").Range("B2:B" & Range("B2").End(xlDown).Row)
Click to expand...

Thanks again repairman, Im so impressed with my form, which is almost completely down to you.

Kind Regards
Martin
 
Upvote 0
Hello Martin,

Try this for question 2:



<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()<br>                <br>                <SPAN style="color:#007F00">' Populates Default Userform Values as " - " or " 1 "</SPAN><br>                <SPAN style="color:#007F00">' Populates "Outgoing Type" Combo List</SPAN><br>        <br>        DateOutgoing1.Value = ""<br>        ValueOutgoing1.Value = ""<br>        OutgoingType1.Value = ""<br>        OutgoingDescription1.Value = ""<br>        Quantity1.Value = 1<br>        WhereFrom1.Value = ""<br>        ForWhat1.Value = ""<br>        ForWho1.Value = ""<br>        <br>        OutgoingType1.List = Worksheets("MacroData").Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).Value<br>              <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


I think this may also answer question 4 if it works.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JohnZ1156
  • Solved
enter formula using activecell and offset
Replies
2
Views
71
JohnZ1156
JohnZ1156
I
  • Question
Copy worksheet but the new worksheet to omit some items
2
Replies
11
Views
415
ipbr21054
I
P
  • Solved
Recorded Macro Ammendments
Replies
3
Views
334
Joe4
Joe4
C
  • Question
When deleting data (rows) macros prompts an error and cannot be used anymore
Replies
1
Views
109
bobaol
B
R
  • Question
Help with Macros
Replies
1
Views
274
t0ny84
T

Forum statistics

Threads
1,214,584
Messages
6,120,384
Members
448,956
Latest member
JPav

Share this page

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