I don't know why but this macro wants to exit excel when I run it

Sub resetpage()
Range("A3:S20").Select
Selection.ClearContents
Range("A3").Select
userform1.Hide


End Sub
I have userform1.hide because I have a worksheetchange event that goes
like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Column = 1 Then
userform1.Show False
myRow = Target.Row
End If
End Sub

When I run Sub resetpage()
it clears the contents and then asks if I want to save changes as if I
had clicked exit

Why is this now happening? it doesn't happen all the time ,but it
happens alot

I don't see anything that would make excel want to quit.

But can you disable events before you clear the contents?

Sub resetpage()
Range("A3:S20").Select
application.enableevents = false
Selection.ClearContents
application.enableevents = true
Range("A3").Select
userform1.Hide
End Sub

This'll stop the worksheet_change event from firing. Is that what you want?

curlydave wrote:

I don't know why but this macro wants to exit excel when I run it

Sub resetpage()
Range("A3:S20").Select
Selection.ClearContents
Range("A3").Select
userform1.Hide

End Sub
I have userform1.hide because I have a worksheetchange event that goes
like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Column = 1 Then
userform1.Show False
myRow = Target.Row
End If
End Sub

When I run Sub resetpage()
it clears the contents and then asks if I want to save changes as if I
had clicked exit

Why is this now happening? it doesn't happen all the time ,but it
happens alot

--

Dave Peterson

Thanks Dave,
The problem seems to be the userform1.hide
I therefore changed the worksheetchangecode to

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
myRow = Target.Row

If Union(Range("$A3:$A20"), Target).Address = Range("$A3:$A20").Address
Then
If Target = "" Then
UserForm1.Hide
Else: UserForm1.Show False
End If
End If

End Sub
this seems to have solved the problem......
Dave Peterson wrote:
I don't see anything that would make excel want to quit.

But can you disable events before you clear the contents?

Sub resetpage()
Range("A3:S20").Select
application.enableevents = false
Selection.ClearContents
application.enableevents = true
Range("A3").Select
userform1.Hide
End Sub

This'll stop the worksheet_change event from firing. Is that what you want?

curlydave wrote:

I don't know why but this macro wants to exit excel when I run it

Sub resetpage()
Range("A3:S20").Select
Selection.ClearContents
Range("A3").Select
userform1.Hide

End Sub
I have userform1.hide because I have a worksheetchange event that goes
like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Column = 1 Then
userform1.Show False
myRow = Target.Row
End If
End Sub

When I run Sub resetpage()
it clears the contents and then asks if I want to save changes as if I
had clicked exit

Why is this now happening? it doesn't happen all the time ,but it
happens alot

--

Dave Peterson

Glad you got it working.

curlydave wrote:

Thanks Dave,
The problem seems to be the userform1.hide
I therefore changed the worksheetchangecode to

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
myRow = Target.Row

If Union(Range("$A3:$A20"), Target).Address = Range("$A3:$A20").Address
Then
If Target = "" Then
UserForm1.Hide
Else: UserForm1.Show False
End If
End If

End Sub
this seems to have solved the problem......
Dave Peterson wrote:
I don't see anything that would make excel want to quit.

But can you disable events before you clear the contents?

Sub resetpage()
Range("A3:S20").Select
application.enableevents = false
Selection.ClearContents
application.enableevents = true
Range("A3").Select
userform1.Hide
End Sub

This'll stop the worksheet_change event from firing. Is that what you want?

curlydave wrote:

I don't know why but this macro wants to exit excel when I run it

Sub resetpage()
Range("A3:S20").Select
Selection.ClearContents
Range("A3").Select
userform1.Hide

End Sub
I have userform1.hide because I have a worksheetchange event that goes
like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Column = 1 Then
userform1.Show False
myRow = Target.Row
End If
End Sub

When I run Sub resetpage()
it clears the contents and then asks if I want to save changes as if I
had clicked exit

Why is this now happening? it doesn't happen all the time ,but it
happens alot

--

Dave Peterson

--

Dave Peterson