Stop DeBug window pop-up
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Stop DeBug window pop-up

  1. #1
    Guest

    Default

    I'm quite new to Excel and have just put together a simple macro which reformats the layout of a sheet depending on the input into a certain cell. It does this using a worksheet.change macro. The problem is, part of the reformatting clears the contents of numerous cells which the worksheet change macro doesn't like and pops up the debug window, and stops the macro executing fully.

    How do I stop this happening?

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    You could probably use:

    On error Resume next
    'Your code
    On error goto 0

    But you should iron the bug out!




  3. #3
    Guest

    Default

    When you say iron out the bug, this is what I want to do, but I don't understand why Excel sees it as a bug. I have worksheet change looking for the input of an # character and if one is used, it reformats the sheet using relative coordinates. This part works fine, but I also need a row to have the contents cleared out. This is when the debug comes up as it appears that a multiple input such as this stops the macro running.

    I can't suss out how to stop this happening.

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Paste the code here and someone will spot the problem

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Manchester, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    userinput = Target.Value
    If userinput = "" Then
    Merge2
    End If
    If userinput = "" Then
    UnMerge
    Else: DoNothing
    End If
    End Sub

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Is there a procedure called DoNothing? If not then you'll hit a problem when the code compiles (Something like Sub or Function not defined). This code will 'do nothing' if the user doesn't type either of the characters you specified:-

    Hope it helps,
    D

    Code:
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    userinput = Target.Value
    If userinput = "" Then
        merge2
    ElseIf userinput = "" Then
        Unmerge
    End If
    End Sub

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Manchester, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There is indeed a procedure called DoNothing. The problem only occurs when trying to clear the contents from multiple cells at once. If cells are cleared individually, then the problem doesn't happen but I want to be able to just select an entire row or column and clear it in one go for the sake of speed.

    [ This Message was edited by: Mr Nick on 2002-03-13 06:08 ]

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this line at the top of your code:-

    If Target.Columns.Count > 1 Or Target.Rows.Count > 1 Then Exit Sub 'More than 1 cell selected

    Now your Merge/Unmerge code will if the user types u or with a hat in a single cell.

    Regards,
    D

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Manchester, UK
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's the bunny!! Thanks for the help. Sheet is now running like a dream.

    Cheers!

  10. #10
    New Member
    Join Date
    Sep 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stop DeBug window pop-up

    I am getting the same pop-up. It does exactly what I want it to, but after protecting the sheet, when I try to clear the contents of ay locked cell (right click, clear contents), the pop up appears. It does not happen if I tab through and del individually.


    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, r As Range
    Set rng = Intersect(Target, Range("Time"))
    If rng Is Nothing Then Exit Sub
    UserInput = Target.Value
    If UserInput > 1 Then
    NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
    Application.EnableEvents = False
    Target = NewInput
    Application.EnableEvents = True
    End If
    End Sub
    Last edited by Shawndapew; Sep 18th, 2014 at 04:03 AM. Reason: typo

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com