Stop DeBug window pop-up

G

Guest

Guest
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?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi

You could probably use:

On error Resume next
'Your code
On error goto 0

But you should iron the bug out!
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top