Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you make a userform open automatically when you open excel? | Excel Worksheet Functions | |||
Sow do i make a userform know if a value is true in a frame, then | Excel Discussion (Misc queries) | |||
how do i make a button from a userform exit onto the document in . | New Users to Excel | |||
How to make userform work publically with hidden sheets? | Excel Discussion (Misc queries) | |||
How to make userform work publically with hidden sheets? | Excel Worksheet Functions |