Hello,
I have this worksheet that when macro is run, a userform will pop up and allow the user to select from a few choices (using commandbuttons), which will then update a bunch of pivot charts and tables based on that buttons tags and activecontrol.name. The code works great. I am now trying to update this with a code that will tell if a specific workbook is open, and if so, then assign the tags and activcecontrol.name based on that user's system username. I am able to assign the userform tag, but I get a "'Run-time error '91': Object variable or With Block variable not set" error when trying to assign the activecontrol.name. I have been unable to find out if this is even possible, and hoping someone can give me some pointers. Below is the relevant code:
Code:
'Main Module
Option Compare Text
Public OEETag As String
Public OEEActiveControl As String
Public Sub OEE4Q()
Dim Ret As Boolean
Dim myForm1 As FilterOEE
Set myForm1 = New FilterOEE
Ret = IsWorkBookOpen("G:\Shared\MOS TBO\OEE\OEE- 4Q1.xlsm")
If Ret = True Then
SupervisorDept
myForm1.Tag = OEETag
Set myForm1.ActiveControl = OEEActiveControl '<--- Error occurs here
GoTo SuperRepGen
End If
myForm1.Show
SuperRepGen:
Select Case myForm1.Tag
'Rest of Code
End sub
---------------------------------------------------------
'sub which assigns tag and activecontrol.name based on username
Sub SupervisorDept()
Debug.Print Username
If Username = "aramaglia" Then
OEETag = "ASY_FIQD"
OEEActiveControl = "FIQD"
End If
End Sub
---------------------------------------------------------
'Functions
Public Function Username()
Username = Environ$("UserName")
End Function
Function IsWorkBookOpen(FileName As String)
Dim ff As Long, ErrNo As Long
On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0: IsWorkBookOpen = False
Case 70: IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function