Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Floating Control (Excel '97 SR2)

    Is there a way to "float" a textbox control over multiple sheets. I have created a workbook that employees may use to enter their company expenses. There are three sheets in the workbook, two of which the users will enter data on. I want to add a textbox control (or something else, suggestions welcome) that will be visible when users switch between sheets, that shows them the current running total of their expenses. I need to be able to display a specific cell's contents as the text.

    Can this be done? Any help would be greatly appreciated.

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Floating Control (Excel '97 SR2)

    A UserForm or message window can float over different worksheets.

    See Jan Karel Pieterse's suggestion at <post#=183435 >post 183435 </post#> and my alternative version at <post#=183447>post 183447</post#> for examples.

    The only difference would be that you DON'T want to close the form or window when deactivating a worksheet!

    Ian.

  3. #3
    New Lounger
    Join Date
    Oct 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Floating Control (Excel '97 SR2)

    Thank you very much for the quick responses. I will most definately look at all the suggestions you two have given me. Personally, the simpler the answer, the better.

    Thanks again.

  4. #4
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Floating Control (Excel '97 SR2)

    You can also try:

    Application.statusbar = Range("A1").value and then put the code in a sheet change activity

    Mario

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Floating Control (Excel '97 SR2)

    Probably the easiest way is to have a textbox on each sheet which is linked to a cell which contains the current expense total. Then use the Selection change event to move the textbox to a convient location. See attached workbook, To view the code for the Meals or Mileage sheet, just right-click on the tab and select View Code. Notice that if you scroll-down a page and select a cell, the textbox moves into view.

    For a fancier floating window, see FloatWnd.zip on Stephen Bullen's Excel Page

    HTH --Sam

    Note, the attachment does not work for me in a browser window, you will need to save the attachement and run it from Excel.

    Edited Mar 13th 2004 to update link
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Floating Control (Excel '97 SR2)

    Find a file called Cellwatch97.exe at:

    http://www.bmsltd.ie/excel/default.htm
    It might be what you need.

    Edited Mar 13th 2004 to update link
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •