Any way to call a worksheet method that may or may not exist?
 

I'm adding a whole bunch of little formatting wedges in VBA on individual
sheets as necessary. I want to call then on workbook save. This works:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Sheet3.DoTheKludges
Sheet4.DoTheKludges
Sheet6.DoTheKludges
Sheet9.DoTheKludges
Sheet10.DoTheKludges

End Sub

But it's irredeemably ugly and unmaintainable and doesn't scale well as
sheets get added to or subtracted from this list.

So I was thinking of something along these lines:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim objWks As Worksheet

For Each objWks In Me.Worksheets

On Error Resume Next
objWks.DoTheKludges
On Error Goto 0

Next

End Sub

Of course, this doesn't work since, I'm guessing, the objWks.DoTheKludges
method call can't be resolved when the runtime compile occurs and that isn't
trapped by the On Error.

Any thoughts how to get around this?

Any way to call a worksheet method that may or may not exist?
 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim objWks As Worksheet

For Each objWks In Me.Worksheets

Application.Run "'" & objWks.Codename & "'!DoTheKludges"
Next

End Sub


--
__________________________________
HTH

Bob

"Dick Watson" wrote in
message ...
I'm adding a whole bunch of little formatting wedges in VBA on individual
sheets as necessary. I want to call then on workbook save. This works:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Sheet3.DoTheKludges
Sheet4.DoTheKludges
Sheet6.DoTheKludges
Sheet9.DoTheKludges
Sheet10.DoTheKludges

End Sub

But it's irredeemably ugly and unmaintainable and doesn't scale well as
sheets get added to or subtracted from this list.

So I was thinking of something along these lines:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim objWks As Worksheet

For Each objWks In Me.Worksheets

On Error Resume Next
objWks.DoTheKludges
On Error Goto 0

Next

End Sub

Of course, this doesn't work since, I'm guessing, the objWks.DoTheKludges
method call can't be resolved when the runtime compile occurs and that
isn't trapped by the On Error.

Any thoughts how to get around this?

Any way to call a worksheet method that may or may not exist?
 

Cool. I'll try it!

"Bob Phillips" wrote in message
...
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim objWks As Worksheet

For Each objWks In Me.Worksheets

Application.Run "'" & objWks.Codename & "'!DoTheKludges"
Next

End Sub

Any way to call a worksheet method that may or may not exist?
 

The 'sheetname'!macroname form was No Joy.

But here's what does work:

On Error Resume Next
Application.Run objWks.CodeName & ".DoTheKludges"
On Error GoTo 0

Thanks again!

"Bob Phillips" wrote in message
...
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim objWks As Worksheet

For Each objWks In Me.Worksheets

Application.Run "'" & objWks.Codename & "'!DoTheKludges"
Next

End Sub

Any way to call a worksheet method that may or may not exist?
 

Isn't that what I said?

--
__________________________________
HTH

Bob

"Dick Watson" wrote in
message ...
The 'sheetname'!macroname form was No Joy.

But here's what does work:

On Error Resume Next
Application.Run objWks.CodeName & ".DoTheKludges"
On Error GoTo 0

Thanks again!

"Bob Phillips" wrote in message
...
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim objWks As Worksheet

For Each objWks In Me.Worksheets

Application.Run "'" & objWks.Codename & "'!DoTheKludges"
Next

End Sub

Any way to call a worksheet method that may or may not exist?
 

.. vs !



Bob Phillips wrote:

Isn't that what I said?

--
__________________________________
HTH

Bob

"Dick Watson" wrote in
message ...
The 'sheetname'!macroname form was No Joy.

But here's what does work:

On Error Resume Next
Application.Run objWks.CodeName & ".DoTheKludges"
On Error GoTo 0

Thanks again!

"Bob Phillips" wrote in message
...
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim objWks As Worksheet

For Each objWks In Me.Worksheets

Application.Run "'" & objWks.Codename & "'!DoTheKludges"
Next

End Sub


--

Dave Peterson

Any way to call a worksheet method that may or may not exist?
 

And lose the single ticks.

"Dave Peterson" wrote in message
...
. vs !

Any way to call a worksheet method that may or may not exist?
 

I concede that the ! was a typo on my part, but I wouldn't the single ticks
mattered. Unnecessary, true, as you wanted the codename, but not a problem.

--
__________________________________
HTH

Bob

"Dick Watson" wrote in
message ...
And lose the single ticks.

"Dave Peterson" wrote in message
...
. vs !