PDA

View Full Version : Solved: Run code when workbook opens



sassora
05-19-2012, 02:21 AM
Hi all,

I have some code that I want to run when the excel file opens.

The VBA below works fine but I'd like to put the bulk of the code in its own sub.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Set shWorkitems = Sheets("Job List")
Set shFolderitems = Sheets("Folder Search")

Select Case Sh.Name
Case shWorkitems.Name
If Target.Column = 10 Then

On Error Resume Next
Application.Goto shFolderitems.Range("A" & Application.Match(Target.Value, _
shFolderitems.Columns(11), 0)), True

Cancel = True
ElseIf Target.Column = 9 Then
Call MainUpdateFolderLog
Cancel = True
End If

Case shFolderitems.Name
If Target.Column = 1 Then

On Error Resume Next
Application.Goto shWorkitems.Range("A" & Application.Match(Target.Offset(0, 10).Value, _
shWorkitems.Columns(10), 0)), True

Cancel = True
End If
End Select

End Sub

The alternative code would look like:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Call RunOnOpenCode

End sub

How would I do this?

Bob Phillips
05-19-2012, 02:43 AM
Your RunOpenCode procedure should have a sh and target argument as the doubleclick procedure does and get passed the values in the call.

You could also call it in Workbook_Open, but what would be the target sheet and cell(s)?

sassora
05-19-2012, 03:20 AM
Sorry, I said on open but I meant on double click.
Let's say the sub I want to call is RunDoubleClick instead.

I should add the inputs to it, what would it look like? Does it include Byval?

Not sure how to feed this into the sub located in a another module.

Thanks for your help.

Bob Phillips
05-19-2012, 08:20 AM
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

Call RunOnOpenCode(Sh, Target, Cancel)
End Sub

Public Sub RunOnOpenCode(ByRef Sh As Object, ByRef Target As Range, ByRef Cancel As Boolean)

Set shWorkitems = Sheets("Job List")
Set shFolderitems = Sheets("Folder Search")

Select Case Sh.Name
Case shWorkitems.Name
If Target.Column = 10 Then

On Error Resume Next
Application.Goto shFolderitems.Range("A" & Application.Match(Target.Value, _
shFolderitems.Columns(11), 0)), True

Cancel = True
ElseIf Target.Column = 9 Then
Call MainUpdateFolderLog
Cancel = True
End If

Case shFolderitems.Name
If Target.Column = 1 Then

On Error Resume Next
Application.Goto shWorkitems.Range("A" & Application.Match(Target.Offset(0, 10).Value, _
shWorkitems.Columns(10), 0)), True

Cancel = True
End If
End Select
End Sub

sassora
05-19-2012, 03:32 PM
Thanks xld

Aussiebear
05-19-2012, 03:45 PM
Kindly mark the thread as solved by going to the Thread Tools dropdown and selecting "Mark As Solved".

sassora
05-20-2012, 11:45 AM
Thanks Aussiebear, I wanted to do this but clicking on "Thread tools" simply directed me to the bottom of the page (to the thread tools section). The menu didn't appear in the way it usually does.