VB Refresh Worksheet Function? - Xtreme Visual Basic Talk

02-26-2004, 08:08 PM

jamesmoore1 jamesmoore1 is offline

Regular

Join Date: Jan 2004

Location: Atlanta, GA

Posts: 55

VB Refresh Worksheet Function?

Is there a way to visually "refresh" a worksheet each time it selected? (not refresh as it applies to pivot tables, etc..)

The issue I am having is that, only in full screen mode, I have two worksheets with VB control buttons on them. I can flip back and forth (using another control) to view page1/page2/page1 etc... After a few times of going back and forth, the area where the control buttons are goes blank(light grey) and nothing can be selected. If I toggle full screen off/on the buttons come back immediately... so I am wondering if there is anything that can be done. I know full screen is a tempermental beast and the user can get by without it, but its sooo nice to have the worksheet in full view on fullscreen.

Any ideas?

02-26-2004, 08:44 PM

Mike Rosenblum Mike Rosenblum is offline

Microsoft Excel MVP

Forum Leader
* Guru *

Join Date: Jul 2003

Location: New York, NY, USA

Posts: 7,848

Huh, I've not experienced this. Not even 100% sure that I understand. Maybe you're running low on memory resources? Does this happen on other PC's if you run it?

Why is FullScreen a "temperamental beast"? I may just not have enough experience with it, I guess, but in what other circumnstances have you noticed problems? (This is just for my own curiousity, if you can think of anything off the top of your head.)

As for your current problem, other than more memory (which may not cure it, but it's still my best guess), I have no other ideas, unfortuantely.

I tried putting this in the 'ThisWorkbook' module:

Code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Application.ScreenUpdating = False
    Application.DisplayFullScreen = Not Application.DisplayFullScreen
    Application.DisplayFullScreen = Not Application.DisplayFullScreen
    Application.ScreenUpdating = True
End Sub

But it creates a noticeable and annoying blink. I was trying to implement the fix you found (programmatically) but it is a failure.

Sorry I can't do better...

-- Mike

02-26-2004, 09:01 PM

jamesmoore1 jamesmoore1 is offline

Regular

Join Date: Jan 2004

Location: Atlanta, GA

Posts: 55

I had hoped it was only on my PC and found out today that my boss had the same problem... thank god I put a Ctrl+ shortcut to a fullscreen toggle that I had created. I'm not discounting that it could be a memory issue, but with 340MB of memory and nothing else running I would hope that I'm not tapped out.

The fullscreen beast comment stemmed from another post I read on the board recently wondering about peoples facination with FullScreen and how if something goes wrong you can really screw up someones computer. I learned this first hand when I was first playing around with FullScreen as well as disabling the standard menu bar at the top so that you basically had a blank screen... until I figured out how to undo that, I lost my worksheet menu bar in excel. Rookie mistake, yes...but it illustrated the point. Here is the code that I use to toggle fullscreen on and off plus hide the menu and command bars.

Code:

Private Sub CmdBtn_FullScrnOff_Click()
  ActiveWorkbook.Unprotect
  Application.CommandBars("Worksheet Menu Bar").Enabled = True
  Application.CommandBars("Full Screen").Enabled = True
  Application.DisplayFullScreen = False
End Sub

Private Sub CmdBtn_FullScrnOn_Click()
  Application.CommandBars("Worksheet Menu Bar").Enabled = False
  Application.DisplayFullScreen = True
  Application.CommandBars("Full Screen").Enabled = False
  ActiveWorkbook.Protect Structure:=False, Windows:=False
End Sub

This is what I meant by fullscreen...EVERYTHING gone. The VB command buttons dissapear after flipping back and forth between the two pages that have them on there. Interestingly... command buttons for macros that Excel has, are not affected by this. hmmmm

Do you know what I am talking about now?

02-26-2004, 09:12 PM

Mike Rosenblum Mike Rosenblum is offline

Microsoft Excel MVP

Forum Leader
* Guru *

Join Date: Jul 2003

Location: New York, NY, USA

Posts: 7,848

I think it mostly, but don't quite get where or what these buttons are... Are the "VB command buttons" on the Worksheet? Or are they CommandBarButtons on a CommandBar (sounds like you've hidden all CB's though).

If it's on a Worksheet, are these buttons from the "Controls Toolbox" CommandBar or from the "Forms" CommandBar?

-- Mike

02-26-2004, 09:18 PM

jamesmoore1 jamesmoore1 is offline

Regular

Join Date: Jan 2004

Location: Atlanta, GA

Posts: 55

They are on the worksheet and came from the control toolbox...they are needed because of the way I have to view the worksheet, the text size on the forms CBs cannot be increased. With the control toolbox CBs, I can format them as if they were on a userform, which solves my problem when the sheet is zoomed out at normal viewing range in order to read the text on the buttons

02-26-2004, 09:29 PM

Mike Rosenblum Mike Rosenblum is offline

Microsoft Excel MVP

Forum Leader
* Guru *

Join Date: Jul 2003

Location: New York, NY, USA

Posts: 7,848

Ok, so it sounds like using buttons from the Forms CommandBar is not practical.

But just for kicks you may want to add one to the Worksheet, see if it does the same thing. If they do not exhibit the same behaviour then you may have a decision to make.

Others may have other ideas, but best I can tell, this is a bug or a memory issue. So you're really going to have to push & pull and stretch the boudaries a bit her and see if you can find some other quirk that you can take advantage of.

Btw, you're not on XL'97 are you?

-- Mike

02-26-2004, 10:09 PM

jamesmoore1 jamesmoore1 is offline

Regular

Join Date: Jan 2004

Location: Atlanta, GA

Posts: 55

Nope using OfficeXP
the CBs from the forms toolbar don't behave the same way... unless someone else has a better idea, I'm probably just going to comment out the section that hides the worksheet menu bar... that seems to be the fly in the ointment... Works fine with that * * * * menu bar at the top LOL.
Thanks for the brainstorming though