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?

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" <(E-Mail Removed)> wrote in
message news:5FF0460F-C980-4BB0-A4A1-(E-Mail Removed)...
> 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?

Cool. I'll try it!

"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

Isn't that what I said?

--
__________________________________
HTH

Bob

"Dick Watson" <(E-Mail Removed)> wrote in
message news:639C6081-F3CD-4D3D-AEE2-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>

.. vs !



Bob Phillips wrote:
>
> Isn't that what I said?
>
> --
> __________________________________
> HTH
>
> Bob
>
> "Dick Watson" <(E-Mail Removed)> wrote in
> message news:639C6081-F3CD-4D3D-AEE2-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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

And lose the single ticks.

"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>. vs !

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" <(E-Mail Removed)> wrote in
message news:uU3cq%(E-Mail Removed)...
> And lose the single ticks.
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>. vs !
>