Asking a user for information and then inserting a row with thatinformation

[Jason Ubalde]

Hello,

I'm trying to create a VBA Macro where the user presses a button on a
spreadsheet which will start a userform. The userform will ask the
user for specific data regarding a spreadsheet with the following
data:

Row Acct Seq Acct-Descr
1 4111 Stuff
2 4112 Stu
3 4112 1 Stuff
4 4112 2 Stuff2
5 4112 3 Stuff3
6 4113 Stuffs
7 4114 Stuffed

The Row # is the rows normally displayed in Excel on the very left. I
would like to the form to ask what Row # the user would like to insert
ABOVE, what # they want in the acct #, what # they want for a seq #
and what the Account Description should be. The Macro will then read
this information, insert the new row and fill in the specified
cells.

I'm very new to VBA (but have done VB waaaaaaaaaaaay back when). Any
help would be greatly appreciated. I've only successfully gotten it
to insert a row after searching for a value but not for a row #.
Later I'm hoping to get the insertion macro to work on other specified
sheets w/ the same exact layout and # of rows.

Thank you again for any help or advice.

-jas

 

[ryguy7272]

This should be pretty close what you want; just make whatever customized
changes you want:

Button Code:
Sub Macro1()
frmPartLoc.Show
End Sub

UserForm Code:
Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")

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

'check for a part number
If Trim(Me.txtPart.Value) = "" Then
Me.txtPart.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtPart.Value
ws.Cells(iRow, 2).Value = Me.txtLoc.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.txtQty.Value

Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("PartsData").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("PartsData").Sort.SortFields.Add
Key:=Range("A2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("PartsData").Sort
.SetRange Range("A2" & iRow)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select

'clear the data
Me.txtPart.Value = ""
Me.txtLoc.Value = ""
Me.txtDate.Value = ""
Me.txtQty.Value = ""
Me.txtPart.SetFocus


End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub

UserForm has 4 TextBoxes: txtPart, txtLoc, txtDate, and txtQty
UserForm also has 2 buttons, named cmdAdd and cmdClose

 

[Gord Dibben]

How to create a UserForm.

http://www.contextures.on.ca/xlUserForm01.html

Should be everything you need at Debra's site.


Gord Dibben MS Excel MVP