Windows Forum

Ask something
Discussion Locked

Very simple VBA commands for Excel?

0 helpful
6 replies

by n2y2

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I had a very simple question working with Excel spreadsheets. I am trying to just add a simple Combo Box into my excel spreadsheet and after going through the VBA Help in Excel as well as several excel books it has really confused me on how to simply add rows of data.

If anyone could throw out an example.. or just let me know the simple VBA command to add in rows of text I would really apprectiate it. Was trying to set up a combo box that chose between the numbers 1 and 30.

Thankyou for your help.

6 replies

by homer simpson instructor at large

I'm not too clear on your question.... But here is some simple code that uses a combination of input boxes to place text into a new row. I have this running off a button on the excel spreadsheet. What are you using the combo box for? To determinehow many rows you want to insert? Or is it to enter data in the newly inserted row? Anyways hope this helps some.

Sub Insert_New_Data()
Dim Value1, Value2, Value3
Selection.Insert Shift:=xlDown
'InputBox # 1
Value1 = InputBox(prompt:="Enter name of new Sales Rep")
ActiveCell.FormulaR1C1 = Value1
ActiveCell.Offset(0, 1).Range("A1").Select
'InputBox # 2
Value2 = InputBox(prompt:="Enter Dept No.")
ActiveCell.FormulaR1C1 = Value2
ActiveCell.Offset(0, 1).Range("A1").Select
'InputBox # 3
Value3 = InputBox(prompt:="Enter Total Sales")
ActiveCell.FormulaR1C1 = Value3
'Copy Comm formula from row above
ActiveCell.Offset(-1, 1).Range("A1").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A2"), Type:= _
xlFillDefault
ActiveCell.Range("A1:A2").Select
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

by n2y2

Sorry for being a little unclear about this question. I am just trying to add a drop down combo box on an excel spreadsheet. What I am having a problem with is the correct commands to just add in the Row Data. I am not sure how to get it to preset to a drop down box for simply picking numbers 1 through 30. Hope this clears it up a bit.

Again, I appreciate the help.

by morriskg

I don't quite think that this is the answer you are looking for, but if you use a Combo Box from the Excel Forms toolbox, rather than one from the Control Toolbox, you set the Row Data by right clicking on the Combo Box and selecting Input Range. In your case this would be a range containing the numbers 1-30.

If you are trying to do data entry and have this combo box in each cell (for a particular column) then you have another problem entirely. You might be able to write some VBA code which makes that Combo Box appear over the current cell if the focus is in the right column. Sounds tricky though ;)

This discussion is closed to new replies.