+ Reply to Thread
Results 1 to 5 of 5

find the last used column on an INACTIVE sheet

  1. #1
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686

    find the last used column on an INACTIVE sheet

    The variable MyInactiveSht is determined by a Listbox on a UserForm

    Please Login or Register  to view this content.
    doesn't work because Address doesn’t work on an inactive sheet

    how can I obtain that cell address?

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Carstowal,

    Since your syntax is correct, I am assuming your problem is with the worksheet name. If you have declared the name as a Public string in a VBA Module, it is visible to all components in the VBA Project as long as the code is running. If you are trying to retrieve the worksheet name from the ListBox on the UserForm then the UserForm must be Loaded for the code below to work. Change the name of the ListBox to match yours.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    Set ScreenUpdating = False, obtain the current sheet name, activate the Inactive sheet, your code, activate the "current sheet name" ???

    HTH

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You don't need to activate the sheet. This will work whatever sheet is active. Just edit how you get the sheet name.

    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Thanks Roy,

    I must admit I was puzzled by this since as you rightly say you don't need sheets or objects to be selected before working with them. However try as I might on my Mac with Excel 2004 for Mac, I could not get this sort of code to work when I was testing it. I've even used your exact example and it just returns the address of the last cell on the Active sheet.

    I'll have to put it down to another frustrating difference between Windows and OSX versions of Excel. Maybe we should have a forum or at least a FAQ for Mac/Windows differences

    Regards,

    Richard

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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