Userform

jose001

Board Regular
Joined
Mar 27, 2006
Messages
103
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 :)
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
You will need to use something along these lines:
Code:
Dim NewSheet As Worksheet
Set NewSheet = Worksheets.Add
NewSheet.Name = TextBox1.Value

Hope that helps :)
 

jose001

Board Regular
Joined
Mar 27, 2006
Messages
103
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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:
Code:
iRow = NewSheet.Cells(Rows.Count, 1).End(xlUp).Row+1
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484

ADVERTISEMENT

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

Board Regular
Joined
Mar 27, 2006
Messages
103
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

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Everywhere you have “ws.Something”, you need to change it to “NewSheet.Something”.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,565
Messages
5,765,147
Members
425,263
Latest member
alcat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top