Make a Better Userform

[oberon.black]

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.

Please Login or Register  to view this content.

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.

[Mike Fogleman]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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 before:=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
>