+ Reply to Thread
Results 1 to 4 of 4

Need help determining why I get an error running a macro on one sheet but not others

  1. #1
    Registered User
    Join Date
    03-07-2011
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2003, 2007, & 2010
    Posts
    52

    Need help determining why I get an error running a macro on one sheet but not others

    So I have some code that will disable all of the input messages in a workbook but it keeps getting hung one sheet in particular and I can't determine why. If I delete that sheet it works fine. Any help troubleshooting the reason it is hanging up is appreciated as I cannot for the life of me figure what is causing it. Working file is attached. Sheet3 ("2.Issue-Task Log") is the one causing the problems. The code is located under the "General" module and launches based on the settings change under the "Settings" worksheet. Thanks.

    Crosspost on OZGrid
    https://www.ozgrid.com/forum/forum/h...but-not-others

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Need help determining why I get an error running a macro on one sheet but not others

    You have several Subs in that module. Specifically what steps do you take to invoke the macro that causes the problem? I did not have any problems upon opening the file, with macros enabled, and I don't know what to do next to reproduce your problem.

    In Sub Unhide_Workbook, variable LastWsName is undefined. That is probably not the problem but I strongly recommend to everyone that they declare variables. Doing so prevents a lot of bugs and runtime errors.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    03-07-2011
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2003, 2007, & 2010
    Posts
    52

    Re: Need help determining why I get an error running a macro on one sheet but not others

    Quote Originally Posted by 6StringJazzer View Post
    You have several Subs in that module. Specifically what steps do you take to invoke the macro that causes the problem? I did not have any problems upon opening the file, with macros enabled, and I don't know what to do next to reproduce your problem.

    In Sub Unhide_Workbook, variable LastWsName is undefined. That is probably not the problem but I strongly recommend to everyone that they declare variables. Doing so prevents a lot of bugs and runtime errors.

    Thanks for taking a look and sorry about the confusion. I separated out the problematic group of macros into a separate module called "ERROR_MACRO" and put the code below for reference, added annotations and reattached the file. The functionality looks something like this:

    1. Disable_Workbook_Input_Messages() - Cycle through each worksheet in a workbook and run the Disable_Workbook_Input_Messages() macro
    2. Disable_Worksheet_Input_Messages() - Select the used range on a worksheet, determine if each cell has validation via the HasValidation() function and turn off the "Input Messages" if it does
    3. HasValidation(Cell As Range) As Boolean - Check if a cell has validation, if so then return a value of true.

    Note that there are also enable versions of the macros that function the same way.

    The error specifically happens when the Disable_Worksheet_Input_Messages() or Enable_Worksheet_Input_Messages() macros goes to run on "Sheet3 (2. Issue-Task Log)". Part way through selecting each cell with validation and disabling/enabling the input message I get

    "Run-time error '1004'
    Application-defined or object-defined error"

    and it highlights the code line "c.Validation.ShowInput = False" which to me is saying it has recognized a cell has validation via the previous (If HasValidation(c) Then) but then when it goes to enable/disable the input message it doesn't recognize the cell reference?

    I have tried undoing all merged cells, changing the table to a range, deleting each row and running the macro to determine if it is a specific cell causing the issue. Nothing seems to work. The oddest part is it doesn't seem to consistently choose a specific cell to error out on. It seems to change. Thanks again for the help.


    DISABLE/ENABLE INPUT MESSAGES IN WORKBOOK CODE (Errors highlighted in red)

    Please Login or Register  to view this content.
    Last edited by drew.j.harrison; 04-05-2019 at 11:21 AM.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Need help determining why I get an error running a macro on one sheet but not others

    I can reproduce your problem and am scratching my head. The sheet is not protected, the cell reference is valid, and the ShowInput attribute is showing up as True, but I don't know why it cannot be changed. Still looking at it.

    By the way you do not have to Select a sheet and then work on ActiveSheet. You can just pass in the Worksheet instead of making it active. Runs faster.

+ 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: 5
    Last Post: 01-16-2015, 03:13 PM
  2. error when running MS Word macro commands in Excel macro
    By bsapaka in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-01-2014, 01:30 PM
  3. Finding Blank cells
    By Sam9 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-12-2014, 06:13 AM
  4. Running a Macro but Sheet name changes; leading to DEBUG error
    By swade730 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-05-2013, 08:40 PM
  5. Error after running macro
    By ciprian in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-19-2011, 10:55 PM
  6. Cannot find macro error when running a macro from a macro in a diffrent workbook.
    By Acrobatic82 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-05-2010, 09:22 AM
  7. Error- Running a Macro on a Protected Sheet
    By nacho in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-20-2007, 08:21 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