Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Disable Workbook event procedure with code?

  Asked By: Bonni    Date: Nov 12    Category: MS Office    Views: 945
  

I have a workbook that contains several worksheets, one of which is
named HQ. I have macros set up to save the file as-is then again
with just the Summary worksheet. The file has a workbook open event
that points to cell A1 of the HQ worksheet. The problem is is that
the HQ is deleted at the end of the process which means when I pull
the resulting Summary file up (open it), it errors out because it's
looking for the HQ worksheet.

Is there anyway to disable the workbook event when the user hits the
Finish button ~or~ add code to the workbook event that
says if there's no HQ worksheet then disregard? What's the best
approach and how do I set that up?

Here's the code for the Finish button:

Sub Finish()
Application.ScreenUpdating = False
MsgBox "You will be prompted to save the file
twice - once as is, once for just the Summary
worksheet"

Call GetSummaryTitle
Call SaveIt '1st save (as-is, with the detail)
Call PrepareSummary
Call DeleteWorksheets
Call SaveIt '2nd save (just the Summary worksheet)

Application.ScreenUpdating = True

End Sub

***

Here's the open event code:

Private Sub Workbook_Open()
aaHQ.Activate
Range("a1").Activate
End Sub

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Douglas Sullivan     Answered On: Nov 12

You need to iterate through the worksheet  names and check for "HQ".

Dim olWorksheet As Worksheet
Dim slWorkSheet As String

For Each olWorksheet In Worksheets
slWorkSheet = UCase(olWorksheet.Name)
If slWorkSheet = "HQ" Then
' Do something.
Else
' Do something else.
End If
Next olWorksheet

 
Answer #2    Answered By: Cambria Lopez     Answered On: Nov 12

I am not sure I understand. You say your worksheet  is called HQ and then in the
open event  procedure it seems that you ask to activate the aaHQ?? This by itself
could be the problem

First you can always "neutralize" this line by putting the ' sign before it.
This will make it a remark line. This should give you a clue if this line is the
problem.

The open  event procedure  is only activating a worksheet and then a cell. So the
question is, if not the HQ worksheet which worksheet would you like to activate?
My questions is: do you actually need this procedure altogether?

Have a look at the following code. I hope this could help
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name = "HQ" Then
ws.Activate
Range("a1").Activate
Exit Sub
End If
Next ws
End Sub

 
Didn't find what you were looking for? Find more on Disable Workbook event procedure with code? Or get search suggestion and latest updates.




Tagged: