Excel Variables 1

[RonRepp]

Hi All:

I have a quandary that I cannot solve. I have an add-in that copies a sheet from the add-in to the activeworkbook. This works great.

On the copied worksheet, I have some code to try and keep it hidden, because it contains encrypted commands.

[code>>Sheet Code]

'FromRPR is a Global boolean variable
If FromRPR Then Exit Sub
Dim Ret As String
If Sheets("P121A").Visible = True Then
Ret = InputBox("Enter password", "RepProductions")
If Ret <> "Password" Then
Sheets("P121A").Visible = False
Else
Sheets("P121A").Visible = True
End If
End If
[/code]

In a form, this is how I copy the sheet:

If tPW.Text = "" Then
    MsgBox "You must supply a password!", vbCritical + vbOKOnly, "RepProductions"
    tPW.SetFocus
    Exit Sub
End If
    MoveHiddenSheet
....

Sub MoveHiddenSheet()
''I'm making my boolean true so that when the sheet is activated in the new workbook, it should exit the sub.
    FromRPR = True
    ThisWorkbook.Sheets("P121A").Copy After:=ActiveWorkbook.Sheets(1)
    ActiveWorkbook.Sheets("P121A").Visible = False
    FromRPR = False
End Sub

I cannot figure out why the boolean variable does not carry over to the sheet object.

Any suggestions?


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[PHV]

I cannot figure out why the boolean variable does not carry over to the sheet object
It's just because FromRPR is defined solely in the add-in VBAProject, not in the ActiveWorkbook's one.
I'd use a cell in the copied sheet to store the boolean value.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RonRepp]

PHV:

I always thought that the project was a whole, including the sheet objects. Your answer makes perfect sense and only wish I'd thought of it.

Thanks...and a star.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net