Compile Error: Variable not defined. (Excel 2007)
Hi there, I've come across a most frustrating issue recently (using Excel 2007) regarding named ActiveX controls that reside on a Worksheet. Everything compiles and runs perfectly on my PC (Windows 7 32-bit/Office 2007) The file(s) in question are .XLS files, and when opened on another PC (Windows 7/Office 2007) as soon as the macros are enabled, it immediately opens the VBA code window up with one of my control names highlighted (e.g. "txtLName1") and shows the error message "Compile error - Variable Not Defined."
I have hunted and hunted for potential solutions, but none that I found worked for me...does ~anybody~ here have any idea how I can fix this? Any help whatsoever would be greatly appreciated!!
Procedure Code:
Public Sub InitBackDoorControls()
txtLName1.Visible = True ' Where highlight appears in context of error message
txtLName1.Text = ""
txtFName1.Visible = True
txtFName1.Text = ""
cbxPlan.Visible = True
Call ShowPlanList
chkFrzOnly.Visible = True
chkFrzOnly.Value = False
cbxFullOrSplit.Visible = True
Call ShowFullOrSplitList
lblPart.Caption = 1
cbxNumDeliveries.Visible = True
Call ShowNumDeliveriesList
cbxHasCP.Visible = True
Call ShowHasCPList
txtLName1.Activate
Range("COST_RATE") = ""
COST_RATE = DEFAULT_COST_RATE
cmdLoadFromFile.Visible = False
End Sub
The issue appears to be the fact that somehow, on a PC other than the one I am working on (where everything works just fine), the names of the ActiveX controls which reside on the Worksheet are being reset to default names i.e. "CommandButton1", "TextBox1" etc...and in that light, the code referencing the control by it's assigned name will trigger the error "Variable not defined." The "Option Explicit" statement seems to make no difference whatsoever.
I have tried deleting *.exd files from %Temp% directories on client PCs...as suggested by Microsoft, and still to no avail!!!
If you need more info from me, please advise...
check if you are missing a
check if you are missing a reference in VBA..
1. open VBA editor
2. stop the code
3. go: tools=> references..
4. look for "missing", then unclick that, and see if you can find a reference that works..
Nick
SOLVED!
Hi Nick, and thanks for the suggestion. However, I had previously looked into that possibility, but it was not the cause of this particular issue.
To Solve:
Visit: http://support.microsoft.com/en-us/kb/3025036
In a nutshell, the nature of this issue had everything to do with the fact that the PC I was modifying the Workbook(s) in question on HAD all of the most recent MS Office Updates...where the Client PC's had not been set to download updates.
After getting all the updates in order on each client PC, I ran the Microsoft FixIt tool (found on the MS support page I provided the link to above) and all is well in the world again :)