Linked Userforms

jose001

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

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Why do you need 2 userforms?

Couldn't everything be done on the one form, perhaps using a multipage control.

By the way you could reduce some of that code.
Code:
With Range("A1:G1")
    .Value = Array("Name", "Current State", "Future State", "Actions", "Deadlines", "Lead", "Project Manager")
    With .Font
        .Name = "Verdana"
        .Size = 10
        .Bold = True
    End With
    With .Interior
        .ColorIndex = 37
        .Pattern = xlSolid
    End With
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .EntireColumn.AutoFit
End With
 

jose001

Board Regular
Joined
Mar 27, 2006
Messages
103
Thanks for that Norie,

It would have been heaps easier to use one form but the powers that be are insisting on 2 giving me a major headache since I know very little VB! I'm thinking of tackling this by selecting the last created sheet with this code:

ActiveWorkbook.Sheets(.Sheets(Worksheets.Count + 1).Name).Select

Ican then get the first box in the first cell but how do I make each input box flow into each cell underneath? Any ideas?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Jose

Maybe I'm missing something but you've got the name of the new sheet in the txtInitiave textbox.

Can't you use that?

You would be able to access it from the 2nd form if you hide the 1st one instead of unloading it.

PS Why are you clearing the values out, there's no need unless the controls are linked to cells.
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
Done properly the powers that be would never know it was a Multipage Form
 

jose001

Board Regular
Joined
Mar 27, 2006
Messages
103
Thats a lot of good points there Norie lol Sorry, I'm extremely new to VB and just learning as I'm going along really so I sure I've made a lot of stupid mistakes in peacing together this code. But your help is really appreciated. I've now hidden the first form instead of unloading it but how do I select the sheet from the value in the txtInitiative box?

Sheets("txtInitiative.value").Select?
 

jose001

Board Regular
Joined
Mar 27, 2006
Messages
103
Yet another stupid mistake duh I don't even need to select the sheet if I haven't unload the form do I. Sorry sorry I'm going striaght to Waterstones after work to buy a VB book promise hehe Thanks for your help Norie its really appreciated!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Well for a start you don't.:)

It's rarely needed to select a sheet to work with it.
Code:
Set NewSheet = Sheets(UserForm1.txtInitiative.Value) ' create reference to new sheet
    Unload UserForm1 ' unload 1st form since we don't need it anymore
    MsgBox NewSheet.Name
 

Forum statistics

Threads
1,181,606
Messages
5,930,869
Members
436,764
Latest member
avalladarez

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