Userform

[jose001]

Hi Everyone,

I'm still having problems with a userform I'm trying to generate in excel, here is the code

Private Sub CommandButton1_Click()

End Sub

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")

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

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtInitiative.Value
ws.Cells(iRow, 2).Value = Me.txtCurrentstate.Value
ws.Cells(iRow, 3).Value = Me.txtFuturestate.Value
ws.Cells(iRow, 4).Value = Me.txtKeymilestone.Value
ws.Cells(iRow, 5).Value = Me.txtMetricsofsuccess.Value
ws.Cells(iRow, 6).Value = Me.txtLeadadmin.Value
ws.Cells(iRow, 7).Value = Me.txtProjectmngr.Value

'clear the data
Me.txtInitiative.Value = ""
Me.txtCurrentstate.Value = ""
Me.txtFuturestate.Value = ""
Me.txtKeymilestone.Value = ""
Me.txtMetricsofsuccess.Value = ""
Me.txtLeadadmin.Value = ""
Me.txtProjectmngr.Value = ""
Me.txtInitiative.SetFocus

End Sub




Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub UserForm_Click()

End Sub

It enters the data from the form into a sheet called 'data' but really I would like it to generate a new worksheet, change the name to whatever is in the txtInitiative field and dump the data in there any ideas? pleeeeaaasse

 

[Lewiy]

You will need to use something along these lines:

Dim NewSheet As Worksheet
Set NewSheet = Worksheets.Add
NewSheet.Name = TextBox1.Value

Hope that helps

 

[jose001]

Hey Lewiy, thanks very much for replying. This code works great but sadly its now stopping at....

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

Any ideas how I can get this new sheet to insert my column headings?

 

[Lewiy]

When you create a new worksheet, the first empty row is always going to be row 1 so if this is what you are trying to achieve then this line of code is not really necessary.

Also worth mentioning is that you need to make sure you are qualifying the correct worksheet. If you used my example as above, then the first empty row on the new sheet would be found with:

iRow = NewSheet.Cells(Rows.Count, 1).End(xlUp).Row+1

 

[jim may]

I'm sorta new to this, but **Backup your data First ** and try (Watch work wrap on Set NWs line -- should be 1 line):

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim NWs As Worksheet

Set NWs = Worksheets.Add(After:=.Worksheets (.Worksheets.Count)).Name = Me.txtInitiative.Value

iRow = 1

'copy the data to the new database sheet
NWs.Cells(iRow, 1).Value = Me.txtInitiative.Value
NWs.Cells(iRow, 2).Value = Me.txtCurrentstate.Value
NWs.Cells(iRow, 3).Value = Me.txtFuturestate.Value
NWs.Cells(iRow, 4).Value = Me.txtKeymilestone.Value
NWs.Cells(iRow, 5).Value = Me.txtMetricsofsuccess.Value
NWs.Cells(iRow, 6).Value = Me.txtLeadadmin.Value
NWs.Cells(iRow, 7).Value = Me.txtProjectmngr.Value

 

[jose001]

Sorry to be a pain guys but I don't seem to be able to get either of these suggestions to work?! Not quite sure where the problem is but heres the code again if you have a chance....


Private Sub CommandButton1_Click()

End Sub

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim NewSheet As Worksheet
Set NewSheet = Worksheets.Add
NewSheet.Name = txtInitiative.Value


'find first empty row in sheet
iRow = NewSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1

'copy the data to the sheet
ws.Cells(iRow, 1).Value = Me.txtInitiative.Value
ws.Cells(iRow, 2).Value = Me.txtCurrentstate.Value
ws.Cells(iRow, 3).Value = Me.txtFuturestate.Value
ws.Cells(iRow, 4).Value = Me.txtKeymilestone.Value
ws.Cells(iRow, 5).Value = Me.txtMetricsofsuccess.Value
ws.Cells(iRow, 6).Value = Me.txtLeadadmin.Value
ws.Cells(iRow, 7).Value = Me.txtProjectmngr.Value

'clear the data
Me.txtInitiative.Value = ""
Me.txtCurrentstate.Value = ""
Me.txtFuturestate.Value = ""
Me.txtKeymilestone.Value = ""
Me.txtMetricsofsuccess.Value = ""
Me.txtLeadadmin.Value = ""
Me.txtProjectmngr.Value = ""
Me.txtInitiative.SetFocus

End Sub




Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub UserForm_Click()

End Sub


How can I get it to insert the column headings into the new worksheet (Initiative, currentstate etc)?

 

[Lewiy]

Everywhere you have “ws.Something”, you need to change it to “NewSheet.Something”.

 

[jose001]

Ooops Thanks you very very much Lewiy!