Return to Main Page
 

In the code I have listed below I would just like to add som
instruction to return back to the CGS sheet after adding in the new S
sheet.


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)

End Sub

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


Please inform me where to insert such a lin

--
oberon.blac
-----------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...fo&userid=2673
View this thread: http://www.excelforum.com/showthread.php?threadid=46662

Return to Main Page
 

Hi Oberon,

Try inserting:

ws.Activate

as a new line before

End Sub

---
Regards,
Norman



"oberon.black"
wrote in message
...

In the code I have listed below I would just like to add some
instruction to return back to the CGS sheet after adding in the new SS
sheet.


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)

End Sub

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


Please inform me where to insert such a line


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