+ Reply to Thread
Results 1 to 2 of 2

Public variable in private routine across worksheets

Hybrid View

  1. #1
    Registered User
    Join Date
    01-08-2014
    Location
    Bath, UK
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    86

    Public variable in private routine across worksheets

    I'm using a double click macro to navigate between sheets but I would like to set up a variable which can be used to detect and return to the original sheet (like a "go back") button.

    I have used code on each of the sheets which starts:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    but I can't find a way to declare a variable (e.g. "originalsheet") which is accessible across all the sheets.

    Having looked around, I understand Dim only works in the same macro or module, so I've tried Public and Static but couldn't get these to do what I wanted/expected.

    Is this possible?

    Thanks!

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Public variable in private routine across worksheets

    In its own module, or at the top of an exisitng module, Public strSheetName as string
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Convert this private sub to public sub to work
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-28-2013, 02:59 PM
  2. [SOLVED] Call Private Sub Routine From Main Macro
    By timbo1957 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-16-2013, 04:50 AM
  3. Public or Private Variable
    By jo15765 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 01-12-2012, 02:46 PM
  4. Option Explicit function and private/public/dim
    By larryg003 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-09-2010, 03:01 PM
  5. Add second private sub routine within a loop
    By rhudgins in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-29-2010, 03:02 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1