Creating controls on sheet results in global variables resetting to non-initialized status

R

rizvisa1

New Member
Joined
Feb 18, 2010
Messages
15
This may have been answered, but I was unable to find the answer, so apologies if it is a repeat.

I am trying to create a activex textbox on a sheet. When I do so, the control gets created and if I do a debug.print, it shows that even after the creation of the control my global variables are still in scope and have the valid values stored. But when the code stops execution, I find that my global variables are no longer have any values as if they were never held any value. So what I am missing. I tried to do that via VBA and I found that I do not have any values in my variables and I tried to do so manually to and ended up with same result. I am using XP and office 2003 and office 2007 and both gives me same behaviour. So what i am missing

Thanks
 
Sort by date Sort by votes
At what level are you dimensioning the variables?
  1. Procedure-Level
  2. Module-Level
  3. Project-Level, Workbook Level, or Public Module-Level


Does your code use the END statement by itself? The END statement by itself will clear global variables.
 
Upvote 0
Workbook level variables
and no, it is simple termination of the routine and is NOT a call to "END".

I was able to find a site that talked about the same issue and from that site, I gather thats how excel will behave. If you add a control, it will recomplile project and your variables are rest to default status. If you do a google search on "Dynamically adding ActiveX controls via VBA kills global VBA heap?" you would find that site.

So the question is what are my options beside not using activeX. ?
 
Upvote 0
Adding controls to a worksheet via the User Interface resets the VBEditor so all the variables are reset with it.

However, adding the controls via code at runtime shouldn't reset the variables.

What code are you using to add the Controls ?
 
Upvote 0
You could create a Named constant (like a Named Range) e.g.;

Code: 
Names.Add "Testing", 500

Creates a named constant called Testing that's equal to 500
 
Upvote 0
In fact I have just tried adding a control via code and it DOES resets the variables. I was wrong.

As suggested by AlphaFrog, if you don't have lots of variables you can temporarly store them somewhere like in names or cells then after the TextBox Control is added put them back in their corresponding variables.
 
Upvote 0
Ok . I have just done some more testing and I discovered something rather curious. If you add the activeX controls via code from outside the Application the variables are preserved !

So, executing the code that adds the Control from a small temporary VBScript seems ideal for this and doesn't require much code.

Example :

The AddTextBoxControl Routine in the code below adds a TextBox Control to the ActiveCell in Sheet1 of the current workbook yet the variables are NOT reset . (Change the textbox destination as required )

Code: 
Option Explicit
 
Private Const VBS_FILE = "C:\AddTextBox.vbs"
 
Sub AddTextBoxControl()
 
    Dim lProcID As Long
 
    'create a temp vbs file on the fly.
    Open VBS_FILE For Output As #1
 
    Print #1, "set wb=Getobject(" & Chr(34) & _
    ThisWorkbook.FullName & Chr(34) & ")"
 
    Print #1, "On Error Resume Next"
    'add the text box control from the vbs file to preserve the variables.
    Print #1, "wb.Sheets(1).OLEObjects.Add (""Forms.TextBox.1"")"
 
    Close #1
 
   'execute the background vbs file.
   Shell "WScript.exe " & VBS_FILE
 
    'allow time for the running of the vbsript.
    Application.OnTime Now + TimeSerial(0, 0, 2), "KillVbsFile"
 
End Sub
 
Private Sub KillVbsFile()
 
    'delete the vbs file.
    Kill VBS_FILE
 
End Sub
 
Upvote 0
very interesting approach Jaafar. Let me see if this works for me too. Thanks for your help. Let me see how it goes. Will post the results/finding.

Thanks for your suggestion too AlphaFrog. I am not able to take your suggestions as I have too many global variables that includes data structures.
 
Upvote 0
Thanks Jaffar, it did work for me too. Really appreciate your help. Just curious, how on earth you even thought of using VBS to create controls like that. It is really a "very outside the box thinking"
 
Upvote 0
rizvisa1 said:
Thanks Jaffar, it did work for me too. Really appreciate your help. Just curious, how on earth you even thought of using VBS to create controls like that. It is really a "very outside the box thinking"
Click to expand...

I first thought of using a diffrent Excel instance and run the code from there because I thought that maybe this would not reset the Client application VBE. Indeed, my suspicion was right !

Rather that running another invisible instance of Excel, running a VBSript is IMO a better and cleaner approach.

Anyway. I am glad it worked for you and am happy that there is now a workaround for solving this challenging and annoying excel limitation.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Referencing cell content as a defined global variable
Replies
0
Views
389
kschmeer
K
T
  • Question Question
thisworkbook.saved changes inexplicably
Replies
9
Views
368
tomluigi
T
hicksi
  • Solved
how to identify control in focus
Replies
7
Views
176
hicksi
hicksi
T
  • Solved
Find Values of Cells and Replace values of Named Cells between Sheets
2 3
Replies
22
Views
577
Peter_SSs
Peter_SSs
S
  • Solved
Newbie: Want to create master spreadsheet that live updates Header Rows to other sheets.
Replies
2
Views
225
Logit
Logit

Forum statistics

Threads
1,226,775
Messages
6,192,932
Members
453,767
Latest member
922aloose

Share this page

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
Back
Top