Public variable does not retain value

Hi Folks,

I have a VBA module that:
1) creates a custom spreadsheet
2) creates a temporary command bar
3) adds vba code to the workbook module for specific events
4) controls toolbars per work sheet using event handlers


When the new custom sheet is created a toolbar is created (if not
already in existence). Near the end of the main module, to activate the
toolbar, I am using 

Code:
--------------------
  
  Worksheets(2).Activate
  Worksheets(1).Activate
--------------------
 
thus triggering the "Workbook_SheetActivate" code (which is
created/added by the mainsubroutine ). 

The "thisWorkbook" module contains: 

Code:
--------------------
  Public SababarIsActive, SababarExists As Boolean
  
  ... other events coded for here ...
  
  Private Sub Workbook_SheetActivate(ByVal Sh As Object) 'SabaFunctionb
  
  Set_Security_Level_Proc_Run
  
  MsgBox ("this is the Workbook_SheetActivate event handler.")
  
  If Range("A1").Value = "Space Air Balance Analysis" Then
  If Application.CommandBars("Sababar").Enabled = False Or _
  Not SababarIsActive Then
  Toggle_CommandBars
  *SababarIsActive = True*
  End If
  
  Set_Security_Level_User
  Exit Sub
  End If
  
  ' deactivate custom controls
  If SababarIsActive Then
  Toggle_CommandBars
  *SababarIsActive = False*
  End If
  
  Set_Security_Level_Off
  
  End Sub
  
  
--------------------
 

I have a pop-up to see that this module is executing upon creation, and
it does. The sheets are switched and the correct toolbar is displayed.

The problem is that the public variables SababarIsActive and
SababarExists do not retain thier values once the main subroutine
completes execution. So, when I change sheets again, the macro has the
opposite value and causes the wrong toolbar to be displayed.

I thought that by setting the value in the Workbook_SheetActivate
subroutine it would be retained throughout, but it seems to be lost
when the main macro completes.

Any help appreciated.

thanks in advance,
Scott


---
Message posted from http://www.ExcelForum.com/

0
2/18/2004 8:08:30 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
560 Views

Similar Articles

[PageSpeed] 17

Scott,

Move the public variables from the ThisWorkbook module to a standard code
module, you will find that they retain okay then.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"hscowan >" <<hscowan.11tbo4@excelforum-nospam.com> wrote in message
news:hscowan.11tbo4@excelforum-nospam.com...
> Hi Folks,
>
> I have a VBA module that:
> 1) creates a custom spreadsheet
> 2) creates a temporary command bar
> 3) adds vba code to the workbook module for specific events
> 4) controls toolbars per work sheet using event handlers
>
>
> When the new custom sheet is created a toolbar is created (if not
> already in existence). Near the end of the main module, to activate the
> toolbar, I am using
>
> Code:
> --------------------
>
>   Worksheets(2).Activate
>   Worksheets(1).Activate
> --------------------
>
> thus triggering the "Workbook_SheetActivate" code (which is
> created/added by the mainsubroutine ).
>
> The "thisWorkbook" module contains:
>
> Code:
> --------------------
>   Public SababarIsActive, SababarExists As Boolean
>
>   ... other events coded for here ...
>
>   Private Sub Workbook_SheetActivate(ByVal Sh As Object) 'SabaFunctionb
>
>   Set_Security_Level_Proc_Run
>
>   MsgBox ("this is the Workbook_SheetActivate event handler.")
>
>   If Range("A1").Value = "Space Air Balance Analysis" Then
>   If Application.CommandBars("Sababar").Enabled = False Or _
>   Not SababarIsActive Then
>   Toggle_CommandBars
>   *SababarIsActive = True*
>   End If
>
>   Set_Security_Level_User
>   Exit Sub
>   End If
>
>   ' deactivate custom controls
>   If SababarIsActive Then
>   Toggle_CommandBars
>   *SababarIsActive = False*
>   End If
>
>   Set_Security_Level_Off
>
>   End Sub
>
>
> --------------------
>
>
> I have a pop-up to see that this module is executing upon creation, and
> it does. The sheets are switched and the correct toolbar is displayed.
>
> The problem is that the public variables SababarIsActive and
> SababarExists do not retain thier values once the main subroutine
> completes execution. So, when I change sheets again, the macro has the
> opposite value and causes the wrong toolbar to be displayed.
>
> I thought that by setting the value in the Workbook_SheetActivate
> subroutine it would be retained throughout, but it seems to be lost
> when the main macro completes.
>
> Any help appreciated.
>
> thanks in advance,
> Scott
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
2/18/2004 9:27:34 AM
Hi Bob,

thanks for the tip (I moved them to a standard module.), but I am stil
encountering the same problems. 
I think the problem is that when I run my main macro, it has contro
over the memory pool or a threading problem.  Perhaps I should hav
specified that the "main macro" is in an Add-In, and th
"Workbook_SheetActivate" procedure is created in a new workbook.

When I run it now, the toolbar is correctly displayed, but the value o
"SababarIsActive" is false once the macro completes. So, this causes a
inverted status condition.

I tried to only activate sheet(2) programatically, and this seems t
allow the public variable to be correctly set to true when 
"physically" activate the custom sheet by clicking the name with th
mouse - this is of course after the main macro has done all it's wor
and only the event handlers are activated.

Is there a way to simulate this? 
Is this a scenario requiring another thread to run after the main macr
has completed?


thanks,
Scot

--
Message posted from http://www.ExcelForum.com

0
2/19/2004 12:44:10 AM
Reply:

Similar Artilces:

Homing Child public folder
Child Public Folder Appears Incorrectly on an Exchange Server. Our root folder is homed in Asia. My impression was when creating a child folder it creates on the server you reside on. It then replicates to the Public Folder then gets Re-homed to what every the parent folder is. This is not happening. Has anybody seen this before? ...

email publications
I have a 4 page newletter that I want to email in the body of the email but only 1 page comes thru. I have pub 2003. Any ideas You can't do it the way you want to. I have a page that will tell you how to do it but it's not something I'd rally want to do. We normally suggest converting the newsletter to a .pdf and attach that to the email. -- JoAnn Paules Microsoft MVP - Publisher How to ask a question http://support.microsoft.com/kb/555375 "sunnyatrodeph" <sunnyatrodeph@discussions.microsoft.com> wrote in message news:880FF3CD-FF2F-4839-A23E-A71F964602C5...

Why won't my 2 publication pages switch to 2-pg view as asked?
This is a brand new publication. It only has two pages to it. There is hardly any content and NO web links. But when I use the VIEW drop down menu and click that very first option saying "Two-page spread", Microsoft Publisher does absolutely NOTHING! No, not in print view ... i'm saying "nothing". Yes, I've restarted my computer... all that. Also, in the master page view many of the options are totally greyed-out. Why doesn't microsoft just fix its software instead of adding new inefficient ones? You must have three or more pages to see a two-page ...

Random value select from a field
Which code or macro should i use to get random item/value from a field in form view? Sorry dobule post but this topic is already lost,did anyone got any idea how to do this?Faster reply or this topic gonna gone again...thanks in advance. ...

Variables in Message Notifications?
All, I have set up some rules for Outlook that give me specific message notifications when messages come in from particular users. I would like to include the subject of the incoming message in the notification. Is such a thing possible? The only way that I can imagine it is through a variable like $subject or some such, but I figured someone here might know. Thanks much, Todd ...

Can you open a publisher 2003 publication in publisher 2000?
Can you open a publisher 2003 document in a publisher 2000 program? No -- JoAnn Paules MVP Microsoft [Publisher] "Shaz" <Shaz@discussions.microsoft.com> wrote in message news:F6732D11-AD6F-4209-891A-CB05BD87ECC3@microsoft.com... > Can you open a publisher 2003 document in a publisher 2000 program? ...

Outlook will not retain passwords
So far I have run the detect & repair utility, put in passwords in program umpteen times, and taken a look at the registry. Nothing helps. I have used the program for years and suddenly is refuses to save any server passwords even though the box is checked. Sometimes it appears to, but if you leave a particular email setup dialog and come back, the line is blank again. Does anyone know what might be happening? There have been several recent issues with passwords not being retained with Outlook. If you're running Outlook 2002, the SP1 solves many of these, so start there. If not...

Checking if values are between values
I'm having a little trouble getting a formula to work. All I need to d is use Countif to determine whether or not a value is within a certai range (1.0-1.9). The formula I've tried is: =countif(a1:a25,>1.0<1.9) I'm sure there's something wrong since it's not working, but hopefull someone can advise me on the correct syntax. This worksheet is being created with Excel 97 -- Message posted from http://www.ExcelForum.com One way: =SUMPRODUCT((A1:A25>1.0)*(A1:A25<1.9)) -- Best Regards Leo Heuser Followup to newsgroup only please. "guilbj2 >"...

No access to Public Folder
Hello There, I have a funky problem regarding Public Folders on a Exchange 2003 Standard. Although I see the Public Folders in Outlook I do not have the rights to add anything. I have set all the rights in Exchange System Manager, but still don't have access. Then I checked with ADSI Edit and found an entry for Public Folder but without an Entry for "Class". Also in the Exchange System Manager under Folders I do not see a "Public Folders" bu cannot creat one, because it is already there (so the warning I get). Does anyone of you have an idea? Thanks and Best Regards Dan...

Null Value in reports
I have a report that has calculations. When a null value is in the calculation, the report displays a '#error' message. How do I get rid of the error and return a blank? Thanks. Look into the NZ and IsNull functions in Help. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Don" wrote: > I have a report that has calculations. When a null value is in the > calculation, the report displays a '#error' message. How do I get rid of the > error and return a blank? > > Thanks. I have fou...

Can both the x and y axes display values?
I understand that the default chart setup in Excel is for the y-axis to display relative values and the x-axis to display categories (often time periods). I want, though, to have both axes display values. Specifically, I want the x-axis to be increments of lactate acid levels and the y-axis to be increments of heart rate. I want to plot a single line that connects points that are intersections of a specific lactic acid level and a specific heart rate. Can I do this and, if so, how do I do this? Thank you in advance. Hi, Use the xy scatter chart type. It has a sub type that c...

XY chart not dropping off to 0, when there is not value
I have a simple xy chart with months on the xxis. I am collecting data as every month goes by. Is there a way to chart all 12 months on X axis and the Y axis to show the plot only when it is non zero. (typically it drops off to 0, when there is not any data and have zero values). Thanks If you have a formula producing zero, replace the zero by NA(). -- David Biddulph "drjayr2002" <drjayr2002@discussions.microsoft.com> wrote in message news:2E0E1AF4-2280-48FB-AFC7-B359F7AAF995@microsoft.com... >I have a simple xy chart with months on the xxis. I am collecting data as &...

graphs showing unwanted zero values
Hi! Here I am once again. I have some graphs in a spreadsheet, and the sourcedata is being filled with values as time goes on and I key in new values every fisrt day of the month. Hence the cells containing future readings is empty. The graph jumps from the latest value lets say 40, and stoops down to zero on the X-axis, and follows it the for the rest of the year. How can I get my graphs to stop showing any line after the latest typed value? I have tried every possible combination when cliking the graph, choose Options on the Tools menu, and the tab named Graph, but in vain. Nothing ...

show new mails in public folder
hello, our public folder dosen't show new mails like in the inbox. I want to see if a new message receive, where can I adjust this. thank's for answeres. best regards Christain In news:DBD8EFB5-F0CA-4705-B9C1-854040BA514E@microsoft.com, Christian Weihs <ChristianWeihs@discussions.microsoft.com> typed: > hello, > > our public folder dosen't show new mails like in the inbox. I want to > see if a new message receive, where can I adjust this. > > thank's for answeres. > > best regards > Christain What version of Outlook are you using? If yo...

Sum Formula Showing Instead of Value :(
I exported a peachtree report to excel. In one colum I did a SUM calculation for the column 2 columns away (ie. in cell J34 I have a Formula =SUM(H3:H34) where they are a column of numbers). The forumula only shows in J34, not the value that should result from the calculation. I have never run into this before. What's going on? I do this all the time. Hmmm... if I type the formula in, I get the answer. If I use the E function to do it, I get only the forumla. Still would like to know what the deal is. I am using Excel 2002. Thanks If a cell is formatted as text and you type a ...

Unable to view public folders
We are currently running Microsoft Exchange 2000. I am currently attempting to create public folders and point a designated email address at them. I am creating each folder via a terminal logged on as Administrator. When I go to the Exchanger manager to view the public folder:- > Administrative Groups > 1st Adminastrive Groups > Public Folders The following message appears:- Enter Network Password Username Password The username field is already populated with the following information:- domanin\user When i attempt to enter the password the message reappears. Hope t...

Public Folder Directory Rights
In a haste I think I removed all directory rights to a branch of my public folders. Now when I try to click directory rights button in the permissions tab within the properties of that folder in Exchange administrator I get "Unable to read security rights form the directory". I also remember inheritant rights prior. Anyone know how I can correct this so that I can fix the PF structure? -- SS I meant to say "I also removed inheritant rights earlier" -- SS "Steven" wrote: > In a haste I think I removed all directory rights to a branch of my public >...

Public Folder
Hello, I have created a Public Folder with a Task View as my MIS Help Desk for my internal users. Occassionally the Task Item will disappear. I DO NOT know why it disappears, can you help me with this? Also, the users only fill in the Subject line and sometimes additional information in the body of the Task. That's all. Very puzzle!? Your help would be greatly appreciated. Thanks. ...

? Manually Cast A Variable<->Raw Data
Hi, I need a way of casting a variable from one type to another and back. The problem is that there is no cast for them to or from each other. Specifically, I need a way of taking a CImageList and simply dumping it to for example, a file. I want to dump the entire variable byte-for-byte. Is there a way to do something like: CImageList m_ImageList; .... BYTE* buf=(BYTE*)m_ImageList; CFile f("c:\\zz", cFile::modeCreate); f.Write(buf, sizeof(buf)); f.Close(); I also need a way of doing the opposite. Something like: BYTE* buf; //read a properly formatted CImageList fr...

Mail merge to to new publication (publisher 2002)
Is it possible in publisher 2002 to save a mail merge document to a new publication in order to edit merged text rather than merge fields as is possible in 2003, or is there another way? Thanks After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Sean <sean30358@hotmail.com>... > Is it possible in publisher 2002 to save a mail merge > document to a new publication in order to edit merged > text rather than merge fields as is possible in 2003, or > is there another way? The Publisher 2002 merge was far more limited than the 2003 merge. -- Ed Be...

Send on bhalf of public folder
I have a user that has full access to a public folder and also send on behalf permissions of the same folder. The user still gets "you do not have permissions to send on behalf of this user" error message. Is there anything I have missed? ...

public contacts / public diary
when i create a new appointment for a contact in a public folder, is it possible to create that appointment in a public folder diary as well as the normal diary? cheers mark You can invite a public calendar folder.=20 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "mark" <mark@remove.com> wrote in message = news:1121421946.30525.0@iris.uk.clara.net... > when i create a new appointment for a contact in a public folder, is = it ...

best way to migrate public folders
Hi all, Now, we are moving exchange 2000 users to exchange 2003 SP2 servers. BTW, what's the best way to migrate all public folders to the new servers? Thanks in advance. On Fri, 4 Aug 2006 09:35:02 -0700, Sally <Sally@discussions.microsoft.com> wrote: >Hi all, > >Now, we are moving exchange 2000 users to exchange 2003 SP2 servers. BTW, >what's the best way to migrate all public folders to the new servers? > >Thanks in advance. > Create replicas on the new server. May want to take a gander at this as well. http://support.microsoft.com/kb/822931/ ...

How can I retain the formatting of the data in a lookup table?
When using the LOOKUP function, is it possible to retain the formatting of the data in the lookup table so that the value returned appears in that same formatting? What I mean is that the sorted table has some items formatted differently (indented slightly and a smaller font), so I want these characteristics retained whenever the LOOKUP function returns these values. Thanks! The short answer is no. Formulas return values only. They can not return formats. -- HTH... Jim Thomlinson "spfowlerOH" wrote: > When using the LOOKUP function, is it possible to ret...

Using A variable for form name
Hi, I have a set of tables and forms with the same names. I've populated a listbox with the form/table names. I'd like to be able to show a form upon clicking the name in the listbox or a button. I've been trying diff things like the following (this is from a button): Text15 = Me.List10.ItemData(List10.ListIndex) a = Text15.Text a.Visible = True which is not working. Is it possible to show a form using a variable? Or is there another way of going about this? Thanks! I'm assuming Text15 is supposed to contain the name of a form? As long as the form is open...