Passing variables from one form to another

[timely2]

I am working with two forms that will feed both a access database and excel spreadsheet. I have one form that obtains information needed for a report on excel and helps my code recognize where to put the information (what cell) based on date and shift. Well I am introducing another form for people that are present for the meeting. I want the date and shift information to pass from the first form to the second form so that it can place the attendence information into a comment in the correct column to align with the rest of the information for that day and shift.

I have the comment section working right just need to make it so it knows where to put the comments.

Basically I do not know how to pass variables from one form so that a later form will be able to call the information supplied from the first form.

Thanks,
Tim

 

[rubbernilly]

me.txt1 = forms!OtherForm.txt1

or, if it is a variable...


me.txt1 = forms!Otherform.strShift

In that case, some procedure will have to write strShift with meaningful data, but I think you want the first option.

 

[timely2]

I am trying to move sCell in this case not to actually be put in the form but to be used with the placement by the VBA. Here is example of the code sCell is what I am bringing over from UseForm1 and using for part of placing the comment in a location.

Private Sub CommandButton1_Click()

Dim oWorkBook As Excel.Workbook
Dim oComment As Excel.Comment
Dim String1 As String, String2 As String
Dim String3 As String, String4 As String, String5 As String
Dim MyString As String, tdate As Date
Dim testers As String


tester = forms!UseForm1.sCell
String1 = TextBox1.Value
String2 = TextBox2.Value
String3 = TextBox3.Value
String4 = TextBox4.Value
String5 = TextBox5.Value
tdate = Now
MyString = tdate & vbLf & vbLf & "Whse Resource: " & String1 & vbLf & "Mfg Resource: " & String2 & vbLf & _
"Whse Lead: " & String3 & vbLf & "Blt TM: " & String4 & vbLf & "Bkl TM: " _
& String5 & vbLf & txt1

Set oComment = Sheets("Sheet1").Range("A4").AddComment(MyString)

With oComment.Shape
.Height = 90
.Width = 140
.TextFrame.Characters.Font.Bold = True
' .TextFrame.Characters(Len(oComment.Text)).Font.Color = RGB(50, 50, 20)
End With
Unload Me

End Sub

 

[timely2]

change out code I had previosly with this.

Set oComment = Sheets("Sheet1").Range("sCell").AddComment(MyString)

 

[rubbernilly]

well, for starters, your variable is dimensioned as "testers" and then you use it as "tester".

Other than that, you should be able to do what you are trying to do, provided that UserForm1 is open when this code runs.

If it is not open, then you might have a public Sub on this second form which takes a value as an argument and writes that value to a Form-Level variable you dimension. Your first form would open the second, then run this public sub, seeding it with the right information. Then use that form level variable in your code, rather than the local.

 

[timely2]

Okay, I know I was not clear on the last one. But, is this kind of what you meant? The first form will already be closed when the second one runs due to functionality of filling a report on excel.

Thanks

dim testers as string

Private Sub CommandButton1_Click()

Dim oWorkBook As Excel.Workbook
Dim oComment As Excel.Comment
Dim String1 As String, String2 As String
Dim String3 As String, String4 As String, String5 As String
Dim MyString As String, tdate As Date

testers = forms!UseForm1.sCell
String1 = TextBox1.Value
String2 = TextBox2.Value
String3 = TextBox3.Value
String4 = TextBox4.Value
String5 = TextBox5.Value
tdate = Now
MyString = tdate & vbLf & vbLf & "Whse Resource: " & String1 & vbLf & "Mfg Resource: " & String2 & vbLf & _
"Whse Lead: " & String3 & vbLf & "Blt TM: " & String4 & vbLf & "Bkl TM: " _
& String5 & vbLf & txt1

End Sub
Public Sub onto()
Set oComment = Sheets("Sheet1").Range(Testers)AddComment(MyString)

With oComment.Shape
.Height = 90
.Width = 140
.TextFrame.Characters.Font.Bold = True
' .TextFrame.Characters(Len(oComment.Text)).Font.Color = RGB(50, 50, 20)
End With
Unload Me

End Sub

 

[rubbernilly]

OK, if the first form has to be closed before the second is even loaded, then you can do it with a global variable.

In a Global Module, put your dimension statement.

Then, the first form writes to that variable before it closes...

Testers = sSomeString

The second form then does not need the line you have of...
testers = forms!UseForm1.sCell


It will already be filled. Just use it in the Excel statement.

Global Variables can be handy, but they can also be troublesome if a procedure inadvertantly sets it. If the value of sSomeString is written anywhere in your database and you can do a DLookup on it, that would be better.