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

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
 
Hi again.

The VBS approach does work for preserving the variables values when an ActiveX Control is added to a worksheet.

But. what's the point of creating a CommandButton if ,for example, we can't sink its Click event ?

I first thought that hooking the Control from the VBScript would be an easy task given the fact that we already have a pointer to the Object but it turned out to be rather difficult .

After much insisting , I discovered that ,crazy as it may sound, toggling the Enable Property of the Control after creating it made the event sinking possible !

here is a taster : ( This will add a CommandButton to Sheets(1) over the Cell D4 and sink the commandbutton Click Event while still not resetting the variables )

Proceedings:


First, add the Event handler code to the worksheet ( Sheet1) module : ( Note the WithEvents CommandButton Variable MUST be declared Public so as to be accessed by the VbSript )

Code:
Public WithEvents CommandButton As CommandButton
 
Private Sub CommandButton_Click()
 
    MsgBox "hello!"
    
End Sub


Now, run the AddCommandButtonControl routine below to create the commandbutton Control :


Code:
Option Explicit
 
Private Const VBS_FILE = "C:\AddTextBox.vbs"

Sub AddCommandButtonControl()
    
    'create a temp vbs file on the fly.
    Open VBS_FILE For Output As #1
    
    'get a pointer to this workbook object interface.
    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, "Set Btn = wb.Sheets(1).OLEObjects.Add (""Forms.CommandButton.1"")"
    
    'define some of the commandbutton properties via the Object Property.
    Print #1, "With Btn"
    
    Print #1, ".Left= wb.Sheets(1).Range(""D4"").Left"
    Print #1, ".Top= wb.Sheets(1).Range(""D4"").Top"
    Print #1, ".Width= wb.Sheets(1).Range(""D4"").Width"
    Print #1, ".Height= wb.Sheets(1).Range(""D4"").Height"
    Print #1, ".Object.Caption= ""Click Me"""
    
    '(Very Important !! ) toggle the Enable Property to take effect.
    'it took me ages to figure this out.
    Print #1, ".Object.Enabled=  False"
    Print #1, ".Object.Enabled=  True"
    'hook the commandbutton.
    Print #1, "Set wb.Sheets(1).CommandButton =.Object"
    
    Print #1, "End With"
    
    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

The only small problem that remains now is that any Public variables that may be defined in the worksheet module ( where the Control is added ie: Sheet1 ) are reset.

Tested on Excel 2003 only.
 
Upvote 0
Ok. to make this task more flexible, I am going to wrap up this thread with this generic routine :

Sub AddControl(ClassType As String, _
ParentSheet As String, _
Optional Left As Double, _
Optional Top As Double, _
Optional Width As Double, _
Optional Height As Double, _
Optional EventSink As String)


Which is supposed to dinamically add a control to the worksheet of your choice , size the control and position it and sink its events. Obviously, all this without resetting any Public variables.

here is a workbook demo .
 
Upvote 0
Hi again.

here is a much cleaner and neater way to add ActiveX controls to a worksheet without resulting in the project being reset and the global variables getting reinitialized :

Here I don't use a VBScript instead I soft reference the worksheet via the CopyMemory API function. This way seems to work by tricking VB into thinking that I am not adding the Controls to the worksheet.

here is a taster :

Place the code in the workbook module , call the AddCommandButtons routine and watch the lModuleLevelVariable variable !

Code:
Option Explicit
 
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
pDest As Any, pSrc As Any, ByVal ByteLen As Long)
 
Private lModuleLevelVariable As Long   [COLOR=seagreen]'var to watch.[/COLOR]
 
Private WithEvents oCommandButton1 As CommandButton
Private WithEvents oCommandButton2 As CommandButton
 
Private Sub oCommandButton1_Click()
    MsgBox "You clicked on : " & oCommandButton1.Name
End Sub
 
Private Sub oCommandButton2_Click()
    MsgBox "You clicked on : " & oCommandButton2.Name
End Sub
 
[COLOR=seagreen]'------------------------------------------------------------[/COLOR]
[COLOR=seagreen]' routine that adds ActiveX controls to a worksheet[/COLOR]
[COLOR=seagreen]' without resetting the project or reinitializing the variables.[/COLOR]
[COLOR=seagreen]'--------------------------------------------------------------[/COLOR]
 
Sub AddCommandButtons()
 
    Dim oTempSht As Worksheet
    Dim lSheetPtr As Long
 
  [COLOR=seagreen]  'initialize our variable before adding the controls.[/COLOR]
    lModuleLevelVariable = 100
 
    MsgBox "Before: " & lModuleLevelVariable
 
   [COLOR=seagreen]'get a memory pointer to the activesheet object.[/COLOR]
    lSheetPtr = ObjPtr(ActiveSheet)
 
 
   [COLOR=seagreen]'copy the sheet memory location to our temp variable[/COLOR]
     CopyMemory oTempSht, lSheetPtr, 4
 
 
   [COLOR=seagreen]'add two commandbuttons and sink their Click eevent.[/COLOR]
    Set oCommandButton1 = _
    oTempSht.OLEObjects.Add("Forms.CommandButton.1").Object
 
    oCommandButton1.Caption = "Click Me 1"
 
    ActiveCell.Offset(4).Select
 
 
    Set oCommandButton2 = _
    oTempSht.OLEObjects.Add("Forms.CommandButton.1").Object
 
    oCommandButton2.Caption = "Click Me 2"
 
   [COLOR=seagreen]'very important to clear the oTempSht object var.[/COLOR]
    [COLOR=seagreen]'not doing it will crash the application !!!!![/COLOR]
    CopyMemory oTempSht, 0&, 4
 
End Sub
 
Sub CheckVariable()
 
    'the module level variable is not reset !
    MsgBox "After: " & lModuleLevelVariable
 
End Sub
 
Upvote 0
Given the re-initializing issue, is there any reason that ActiveX controls would be preferable to Forms menu controls?
 
Upvote 0
As usually - very interesting trick, Jaafar! :)

Often the ideas on borders of the different knowledges give unexpectedly efficient decisions.
Thank you for sharing those one!

For more safety I would block code interrupting:
Rich (BB code):

  ' Just for the safety - block code interrupting
  On Error Resume Next
  Application.EnableCancelKey = xlDisabled

  'copy the sheet memory location to our temp variable
  CopyMemory oTempSht, lSheetPtr, 4
  
  ' ... the code ...
  
  'very important to clear the oTempSht object var.
  'not doing it will crash the application !!!!!
  CopyMemory oTempSht, 0&, 4
  
  ' Release code interrupting 
  Application.EnableCancelKey = xlErrorHandler

Note: the known disadvantage of dynamic adding of ActiveX controls to the sheet or to the user form is the memory leakage.

Thanks & Regards,
 
Last edited:
Upvote 0
Given the re-initializing issue, is there any reason that ActiveX controls would be preferable to Forms menu controls?

Hi Mike.

well, the ActiveX controls are event-rich as opposed to form menu controls. that's the main reason for using them I guess.

In fact, the main reason I digged into this is probably out of curiosity and for learning reasons.
 
Upvote 0
Good solution Jaafar!

I tried your code and it worked perfectly.

Now i have a silly question: how can i delete, using VBA, the OLEobject, since i could not select it on the worksheet?
 
Upvote 0
Hi Mike.

well, the ActiveX controls are event-rich as opposed to form menu controls. that's the main reason for using them I guess.

In fact, the main reason I digged into this is probably out of curiosity and for learning reasons.
Curiosity and learning are good reasons. :)
 
Upvote 0
Thanks all.

Just worth mentioning a remaining problem which i am hoping can be solved.

If the worksheet contains any pre-existing runtime controls then the CopyMemory based routine ( AddCommandButtons ) fails to preserve the variables.

Still investigating why this happens.
 
Upvote 0

Forum statistics

Threads
1,226,812
Messages
6,193,121
Members
453,777
Latest member
Miceal Powell

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