Hi all,
I have a userform which, at the moment, creates a new sheet in excel, renames the sheet with a value from the userform, inserts headers into that sheet and then plops the information from the form into the row underneath the headers (hope you're still with me!!)
This is all working quite nicely but the project has changed and I now have to create a two part userform, the second part of which will put information back into the created sheet in specific coloumns! I have created the new form but have no idea how to link them. Here is the code for the first form..
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim NewSheet As Worksheet
Set NewSheet = Worksheets.Add
NewSheet.Name = txtInitiative.Value
'Headers
Range("A1").Select
ActiveCell.FormulaR1C1 = "Name"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Current State"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Future State"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Actions"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Deadlines"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Lead"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Project Manager"
Range("A1:G1").Select
With Selection.Font
.Name = "Verdana"
.Size = 10
End With
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
Selection.Font.bold = True
Columns("A:G").Select
Selection.ColumnWidth = 18.71
Selection.ColumnWidth = 20.57
Selection.ColumnWidth = 22.43
Range("A1").Select
'find first empty row in sheet
iRow = NewSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
'copy the data to the sheet
NewSheet.Cells(iRow, 1).Value = Me.txtName.Value
NewSheet.Cells(iRow, 2).Value = Me.txtCurrentstate.Value
NewSheet.Cells(iRow, 3).Value = Me.txtFuturestate.Value
NewSheet.Cells(iRow, 6).Value = Me.txtLeadadmin.Value
NewSheet.Cells(iRow, 7).Value = Me.txtProjectmngr.Value
'clear the data
Me.txtInitiative.Value = ""
Me.txtCurrentstate.Value = ""
Me.txtFuturestate.Value = ""
Me.txtLeadadmin.Value = ""
Me.txtProjectmngr.Value = ""
Me.txtInitiative.SetFocus
Unload Me
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub UserForm_Click()
End Sub
The next userform has 12 boxes, six on one side titled 'actions' and 6 on the other titled 'deadlines'. My problem is this, how can i get the information from this new form to flow into the created sheet without knowing what it is called beforehand? The boxes titled actions will need to flow into column 'D' starting at D2 and then into each cell underneath down 6 cells and the same with Deadlines next to it. Doea anyone have any idea how to do this because I'm completely at a loss!
I have a userform which, at the moment, creates a new sheet in excel, renames the sheet with a value from the userform, inserts headers into that sheet and then plops the information from the form into the row underneath the headers (hope you're still with me!!)
This is all working quite nicely but the project has changed and I now have to create a two part userform, the second part of which will put information back into the created sheet in specific coloumns! I have created the new form but have no idea how to link them. Here is the code for the first form..
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim NewSheet As Worksheet
Set NewSheet = Worksheets.Add
NewSheet.Name = txtInitiative.Value
'Headers
Range("A1").Select
ActiveCell.FormulaR1C1 = "Name"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Current State"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Future State"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Actions"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Deadlines"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Lead"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Project Manager"
Range("A1:G1").Select
With Selection.Font
.Name = "Verdana"
.Size = 10
End With
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
Selection.Font.bold = True
Columns("A:G").Select
Selection.ColumnWidth = 18.71
Selection.ColumnWidth = 20.57
Selection.ColumnWidth = 22.43
Range("A1").Select
'find first empty row in sheet
iRow = NewSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
'copy the data to the sheet
NewSheet.Cells(iRow, 1).Value = Me.txtName.Value
NewSheet.Cells(iRow, 2).Value = Me.txtCurrentstate.Value
NewSheet.Cells(iRow, 3).Value = Me.txtFuturestate.Value
NewSheet.Cells(iRow, 6).Value = Me.txtLeadadmin.Value
NewSheet.Cells(iRow, 7).Value = Me.txtProjectmngr.Value
'clear the data
Me.txtInitiative.Value = ""
Me.txtCurrentstate.Value = ""
Me.txtFuturestate.Value = ""
Me.txtLeadadmin.Value = ""
Me.txtProjectmngr.Value = ""
Me.txtInitiative.SetFocus
Unload Me
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub UserForm_Click()
End Sub
The next userform has 12 boxes, six on one side titled 'actions' and 6 on the other titled 'deadlines'. My problem is this, how can i get the information from this new form to flow into the created sheet without knowing what it is called beforehand? The boxes titled actions will need to flow into column 'D' starting at D2 and then into each cell underneath down 6 cells and the same with Deadlines next to it. Doea anyone have any idea how to do this because I'm completely at a loss!