Make a Better Userform
 

I have a userform that allows me to add the last and first names of my
clients. Below is the code for the cmdbutton on that userform.


Code:
--------------------

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim newSheetName As String
Set ws = Worksheets("CGS")

'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.LstNm.Value) = "" Then
Me.LstNm.SetFocus
MsgBox "Please enter last name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.LstNm.Value
ws.Cells(iRow, 5).Value = Me.FrstNm.Value
newSheetName = ws.Cells(iRow, 1) & "," & ws.Cells(iRow, 5)

'clear the data
Me.LstNm.Value = ""
Me.FrstNm.Value = ""
Me.LstNm.SetFocus

For Each ws In Worksheets
If ws.Name = newSheetName Or _
newSheetName = "" Or _
IsNumeric(newSheetName) Then
MsgBox "Sheet already exists or name is invalid", vbInformation
Exit Sub
End If

Next
Sheets("SS").Visible = xlSheetVisible
Sheets("SS").Copy befo=Sheets(1)
Sheets("SS").Visible = xlSheetVeryHidden
Sheets(1).Name = newSheetName
Sheets(newSheetName).Move After:=Sheets(Sheets.Count)

'close userform
Unload Me
End Sub

--------------------


I would like to add the ability to have that client information added
to another worksheet within my workbook (worksheet 'at'). I would like
for it to start the insert at cell 'b10'.

I would also like for the userform to return me back to worksheet 'CGS'
before it closes.

Please keep explanations very simple. I am new at this and I ask tons
of questions simply because I am taken by the ablity to do this type of
work.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=466691

Make a Better Userform
 

You already have the mechanics in place to add the info to CGS, create two
new variables for the second worksheet AT.
Dim ws1 As Worksheet
Dim iRow1 As Long
Set ws1 = Worksheets("AT")
'find first empty row in AT, column 2

If ws1.Range("B10").Value = "" Then
iRow1 = ws1.Cells(Rows.Count, 2) _
.End(xlUp).Row + 9 'adjust if there is data _
'in B1:B9
Else
iRow1 = ws1.Cells(Rows.Count, 2) _
.End(xlUp).Row + 1
End If

'copy data to AT (ws1) at the same time you copy to database.
'at end of sub: Worksheets("CGS").Activate (or .Select)



Mike F



"oberon.black"
wrote in message
news:oberon.black.1v86yb_1126494320.6626@excelforu m-nospam.com...

I have a userform that allows me to add the last and first names of my
clients. Below is the code for the cmdbutton on that userform.


Code:
--------------------

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim newSheetName As String
Set ws = Worksheets("CGS")

'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.LstNm.Value) = "" Then
Me.LstNm.SetFocus
MsgBox "Please enter last name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.LstNm.Value
ws.Cells(iRow, 5).Value = Me.FrstNm.Value
newSheetName = ws.Cells(iRow, 1) & "," & ws.Cells(iRow, 5)

'clear the data
Me.LstNm.Value = ""
Me.FrstNm.Value = ""
Me.LstNm.SetFocus

For Each ws In Worksheets
If ws.Name = newSheetName Or _
newSheetName = "" Or _
IsNumeric(newSheetName) Then
MsgBox "Sheet already exists or name is invalid", vbInformation
Exit Sub
End If

Next
Sheets("SS").Visible = xlSheetVisible
Sheets("SS").Copy befo=Sheets(1)
Sheets("SS").Visible = xlSheetVeryHidden
Sheets(1).Name = newSheetName
Sheets(newSheetName).Move After:=Sheets(Sheets.Count)

'close userform
Unload Me
End Sub

--------------------


I would like to add the ability to have that client information added
to another worksheet within my workbook (worksheet 'at'). I would like
for it to start the insert at cell 'b10'.

I would also like for the userform to return me back to worksheet 'CGS'
before it closes.

Please keep explanations very simple. I am new at this and I ask tons
of questions simply because I am taken by the ablity to do this type of
work.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile:
http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=466691