Add a combo list to Input Box?

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.
 
Perhaps for number 3:


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CmdAdd_Click()<br>                <br>                <SPAN style="color:#007F00">' Button inserts all data onto the databases,</SPAN><br>                <SPAN style="color:#007F00">' Inserts " - " If field left blank ensuring cell is classed as used.</SPAN><br>                <SPAN style="color:#007F00">' Then unloads</SPAN><br>     <br>    <SPAN style="color:#00007F">If</SPAN> DateOutgoing1.Value <> "" <SPAN style="color:#00007F">Then</SPAN><br>        Sheets("Income - Outgoings").Range("A" & Range("A1").End(xlDown).Row).Offset(1, 0).Value = _<br>        DateOutgoing1.Value<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Sheets("Income - Outgoings").Range("A" & Range("A1").End(xlDown).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:#007F00">'    If ValueOutgoing1.Value <> "" Then</SPAN><br><SPAN style="color:#007F00">'        Sheets("Income - Outgoings").Range("B" & Range("B" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = _<br>'        ValueOutgoing1.Value</SPAN><br><SPAN style="color:#007F00">'    Else</SPAN><br><SPAN style="color:#007F00">'        Sheets("Income - Outgoings").Range("B" & Range("B" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = _<br>'        " - "</SPAN><br><SPAN style="color:#007F00">'    End If</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">'    If OutgoingType1.Value <> "" Then</SPAN><br><SPAN style="color:#007F00">'        Sheets("Income - Outgoings").Range("F" & Range("F" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = _<br>'        OutgoingType1.Value</SPAN><br><SPAN style="color:#007F00">'    Else</SPAN><br><SPAN style="color:#007F00">'        Sheets("Income - Outgoings").Range("F" & Range("F" & Rows.Count).End(xlUp).Row).Offset(1, 0).Value = _<br>'        " - "</SPAN><br><SPAN style="color:#007F00">'    End If</SPAN><br>    <br><br>    <br>        Unload Me<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


I cannot seem to remember what the code in your question 1 was for...:eek:
maybe it is extra?

The commented top part shows a different way to reference table using the table name and header name though. :)
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi Jeff,

Thank You those two worked perfectly,

I feel cheeky for asking, but is it possible to ensure that the table expands rather than having to add rows above the total bar for the macro to fill?

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("A1").End(xlDown).Row).Offset(1, 0).Value = _
        DateOutgoing1.Value
    Else
        Sheets("Income - Outgoings").Range("A" & Range("A1").End(xlDown).Row).Offset(1, 0).Value = _
            " - "
    End If


And finally I have added code to the Add New Type Button, although it add to the list, it does not get populated in the current combo box along with the others from the list, but rather waits until the form is opened a second time.

Is it possible to reload the AddOutgoing Form including the current text IE not the defaults.



Code:
Private Sub AddNewOutgoingType1_Click()
                
                ' Button inserts all "New Outgoing Payments" data into the Tables in databases,
                ' Inserts " - " If field left blank ensuring cell is classed as used.
                ' Then unloads
                
        If NewOutgoingType1.Value <> "" Then
        Sheets("MacroData").Range("B" & Range("B2").End(xlDown).Row).Offset(1, 0).Value = _
        NewOutgoingType1.Value
    Else
        Sheets("MacroData").Range("B" & Range("B2").End(xlDown).Row).Offset(1, 0).Value = _
            " - "
    End If
    
        Unload Me

End Sub

Private Sub UserForm_Initialize()
                
                ' Populates Default Userform Values as " - " or " 1 "
                
        NewOutgoingType1.Value = ""

End Sub

Private Sub CmdCancel_Click()
        
            ' Closes the UserForm.
        
        Unload Me

End Sub

Private Sub CmdClear_Click()
        
            ' Clears the UserForm.
        
        Call UserForm_Initialize

End Sub


Thanks again these currently work great and will save me lots of time in the future, and Ive been able to make another form for income, and plan to change the simple forms I have to Add a customer which I think I will be able to by adapting and using the code You have helped me with here.

Kind Regards
Martin
 
Upvote 0
Hello Martin,

No need to feel cheeky :LOL:, (I love that term...(must be UK, huh))



However I am not sure about forcing the resize. From my experiance, placing a value below will cause a table to auto-resize.



Below you will find a bit of code to place into the worksheet module.
Copy the below
Right click the macro data tab (This is the sheet with the list in B2:B?)
Choose Veiw Code

Paste in the code:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#00007F">Dim</SPAN> Changed <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, Range("B" & Range("B2").End(xlDown).Row))<br><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">'''Not completely sure here.</SPAN><br>        OutgoingType1.List = Worksheets("MacroData").Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).Value<br><br>            <SPAN style="color:#007F00">'''if not perhaps:</SPAN><br>            <SPAN style="color:#007F00">'UserForm1.Repaint</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Not completly sure, yet it may give you some ideas. I am truely not sure about the repaint, but worth a shot.



________________________________________________________________



Also, another neat feature is the built in dataform. Worth knowing about if you have never came across it.

to test, paste the below into a new workbook. Note: This must be pasted into cell A1,A2,B1, or B2.

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #75923c; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 height=21 width=64>first</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #75923c; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=64>second</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #75923c; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=64>thrird</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #75923c; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=64>forth</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #75923c; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl74 width=64>fifth</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl71 align=right>13</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl65 align=right>14</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=21 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>6</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>9</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>12</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl68 align=right>15</TD></TR></TBODY></TABLE>

Alt+F11 to open the VBEditor
Keystrokes Alt - I - M to insert module

Paste in:
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> sho()<br><br>ActiveSheet.ShowDataForm<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

F8 to run sho or the playbutton to see.

:)
 
Upvote 0
Edit the above:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#00007F">Dim</SPAN> Changed <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, Range("B" & Range("B2").End(xlDown).Row))<br><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">'''Not completely sure here.</SPAN><br>        OutgoingType1.List = Worksheets("MacroData").Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row).Value<br><br>            <SPAN style="color:#007F00">'''if not perhaps:</SPAN><br>            <SPAN style="color:#007F00">'UserForm1.Repaint</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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