+ Reply to Thread
Results 1 to 9 of 9

Convert Macro to Execute Upon Worksheet Activate

  1. #1
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Convert Macro to Execute Upon Worksheet Activate

    Hi,

    The macro below works perfectly. After I edit an individual cell in column B the macro looks at the value in column A, for example,

    2.1
    2.3.4.7.3
    4.2.5
    5.2.4

    The macro then indents the text in column B according to the number of dots in the string in column A.

    I would like to have this macro execute and apply to all populated cells in column B starting at row 9 but I cannot figure out how to convert this to a worksheet_activate() macro.

    Your suggestions would be most appreciated.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Convert Macro to Execute Upon Worksheet Activate

    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Convert Macro to Execute Upon Worksheet Activate

    You can leave your worksheet change code exactly as it is. Include the following in the worksheet code area as well:

    Please Login or Register  to view this content.
    You now have two event macros that will work together! When the worksheet is activated, the activate macro begins to work its way down column B, re-entering the values that are already there. Each time a value is re-entered, your macro will detect the change in the column B cell and set the indent.
    Gary's Student

  4. #4
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Re: Convert Macro to Execute Upon Worksheet Activate

    Hi ProtonLeah,

    Thanks for the quick reply. Got an error "run-time error '91': Object variable or With block variable not set." Did I do something wrong?

  5. #5
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Re: Convert Macro to Execute Upon Worksheet Activate

    Hi Jakobshavn and ProtonLeah,

    A couple issues...first the error mentioned above but than another that dawned on me when I read Jacobshavn's post: I don't want to indent this stuff every time the page is opened. That is a distinction I overlooked when simply suggesting the macro be switched form change to activate. So, if there are spaces at the left side of column B, we need to skip the line. (there will be no lines that are never indented.)

    Thoughts?

  6. #6
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Re: Convert Macro to Execute Upon Worksheet Activate

    Error: Object variable or With block variable not set.

    this is the line it objects to: Lastrow = Cells(Rows.Count, "B").End(xlUp)


    Please Login or Register  to view this content.

  7. #7
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Convert Macro to Execute Upon Worksheet Activate

    If Lastrow is a Range, you need a Set.

  8. #8
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Re: Convert Macro to Execute Upon Worksheet Activate

    I am quickly out of my league...how do I do Set?

    Thanks!

  9. #9
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Convert Macro to Execute Upon Worksheet Activate

    Please Login or Register  to view this content.
    as a replacement line.

+ 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. Replies: 3
    Last Post: 10-10-2012, 05:10 PM
  2. [SOLVED] Click button in one worksheet to activate Macro in another
    By guvner5 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-05-2012, 11:39 AM
  3. To Activate worksheet/tab in Macro
    By KFT in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-04-2008, 04:23 PM
  4. [SOLVED] How to execute a macro in a protected worksheet?
    By Ha Noi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-12-2006, 01:05 PM
  5. Zoom via Macro for WorkSheet to activate?
    By frenic in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-18-2005, 12:05 AM

Tags for this Thread

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