Sign in
 
Home20132010Other VersionsLibraryForumsGallery
TechCenter >
> 
> 
Using a sub from a worksheet in a Private Sub Workbook_Open in the 'ThisWorkbook'

Answered Using a sub from a worksheet in a Private Sub Workbook_Open in the 'ThisWorkbook'

  • Wednesday, May 23, 2012 8:46 PM
     
     
    Sign In to Vote
    0

    Hello,

    I have been working on several Subs (All of what I am doing is in VBA) that are used to format certain cells in a worksheet depending on the user's choice on messageboxes that autorun when they open the workbook. I got everything to work well on the test sheet, but when I went to put it into the actual workbook I need it in, I cannot prevent it from running the format cells on both worksheets. I only want it to run on one so I figured I had to put all the code into the specific worksheet. That caused it to not run the macros upon opening the workbook. I am using Private Sub Workbook_Open() to get the macros to autorun, but that does not work (even with Private Sub Worksheet_Open() ) without having all the Subs in the 'ThisWorkbook' module, and that causes the macros to run on all sheets. Anybody have any suggestions?

    Thank you,

    John G

     

All Replies

  • Thursday, May 24, 2012 1:30 AM
     
     Answered
    Sign In to Vote
    1

    Hi

    Use Private Sub Workbook_Open() and somewhere at the beginning select your sheet name" Worksheets("Sheet1").Select" change it to your sheet name

    HTH

    Cimjet

    Cimjet

    • Marked As Answer by JJG1118 Thursday, May 24, 2012 4:34 PM
    •  
     
  • Thursday, May 24, 2012 4:35 PM
     
     
    Sign In to Vote
    0

    It did the trick, I had to tweak a couple of statements later on that caused breaks (couldn't use .ClearContents because of an error 400: cannot change value of merged cell, had to use .Value = "" instead). Other than that, worked well. 

    Cheers.

    John G