Stop DeBug window pop-up

Mar 12th, 2002, 08:36 AM

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?

Dave Hawley · Mar 12th, 2002, 08:39 AM

Hi

You could probably use:

On error Resume next
'Your code
On error goto 0

But you should iron the bug out!

Mar 12th, 2002, 08:45 AM

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.

Dave Hawley · Mar 12th, 2002, 08:46 AM

Hi

Paste the code here and someone will spot the problem

Mr Nick · Mar 13th, 2002, 05:07 AM

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

dk · Mar 13th, 2002, 06:57 AM

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

Mr Nick · Mar 13th, 2002, 07:07 AM

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 ]

dk · Mar 13th, 2002, 08:05 AM

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

Mr Nick · Mar 13th, 2002, 09:04 AM

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

Cheers!

Mar 13th, 2002, 06:57 AM	#6
dk MrExcel MVP Join Date: Feb 2002 Location: Sydney, Australia Posts: 2,908	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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 12th, 2002, 08:36 AM	#1
Guest Posts: n/a	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?

Mar 12th, 2002, 08:39 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi You could probably use: On error Resume next 'Your code On error goto 0 But you should iron the bug out!

Mar 12th, 2002, 08:45 AM	#3
Guest Posts: n/a	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.

Mar 12th, 2002, 08:46 AM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Paste the code here and someone will spot the problem

Mar 13th, 2002, 05:07 AM	#5
Mr Nick Board Regular Join Date: Mar 2002 Location: Manchester, UK Posts: 95	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

Mar 13th, 2002, 07:07 AM	#7
Mr Nick Board Regular Join Date: Mar 2002 Location: Manchester, UK Posts: 95	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 ]

Mar 13th, 2002, 08:05 AM	#8
dk MrExcel MVP Join Date: Feb 2002 Location: Sydney, Australia Posts: 2,908	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

Mar 13th, 2002, 09:04 AM	#9
Mr Nick Board Regular Join Date: Mar 2002 Location: Manchester, UK Posts: 95	That's the bunny!! Thanks for the help. Sheet is now running like a dream. Cheers!