hi all:

i have a bunch of XLS files that have VBA code in them that have
already been sent out to another consultant (all are hospital
specific) and i was notified earleir today that a "button" on one tab
does not work

i verified that it does not work (had forgot to update the assigned
macro to reflect new tab names)

so ... i was hoping to send out a very simple XLS file w/ just one
macro inside it ... and have the other consultant simply call that
macro from within each of the hospital specific XLS file to change the
undering macro to make the button work as designed

can i do that? .... am at a loss so far (i've done other "fix" XLS
files, but those changes were always to the workbooks proper, changing
formulas, etc.

thanks in advance

What do you mean when you say "new tab names"? If you're referring to
worksheet tabs then your project needs a serious overhaul if it doesn't
work because a user renamed a sheet! OR do you intend to distribute a
'bandaid' for every user who renames a sheet?

Better (IMO) to properly rebuild your project!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Mon, 05 Jan 2015 23:52:22 -0500, GS wrote:

What do you mean when you say "new tab names"? If you're referring to
worksheet tabs then your project needs a serious overhaul if it doesn't
work because a user renamed a sheet! OR do you intend to distribute a
'bandaid' for every user who renames a sheet?

Better (IMO) to properly rebuild your project!!!

hi grary - thanks for the reply

i guess i was trying to be too parsimonious; sorry

<verbose on :-)

these hospital sepcific spreadsheets contains about 30 tabs of
finacial data based on a report the hospitals submit to the State ..
they have to submit this reports 45 days after fiscal year end ...
what we do is, a year or so later, take Medicaid paid claims (and AR
and denied days data from the hospitals) to get an exact Medicaid Net
Revenue (which resides on yet another, non-State-report, "tab") and
uise that to "redistribute" everything (based on existing ratios,
etc.) not only across all the Medicaid financial categories, but also
across the different payors (e.g., Mediicare, Commerical Inusrance,
Self-Pay, etc., such that Total Net Revenues, i.e., across ALL
payores, remains the same) ... and then we re-subnmit this "revised"
report to the State (note: this State mandated financial report is
used to determine qualification for a Mediciad "Disproportionate
Share" supplemental reimbursement pool (about 140-150 out of 450+
Calif. hospitals typically qualify for about $1.1B in supp revnue)
..... so, in my hosp-secific spreadsheets, think of all those tabs as
original and final ... and i send these off to a couple of other
consultants (who do the in-hospital data pulling, and final assembling
of all data)

as such, there's all sorts of "buttons" that do certain things to make
life easier for these other guys

and for one of those buttons' code, i forgot to update it to reflect
the new (fiscal year) tab name, CY13 Final:


Sub CopyFinalRevsToNewTotals()
'
' Copy Revs from CY12 Final to 12 New Totlas
'
'
Sheets("CY12 Final").Select
Range("B8:X19").Select
Selection.Copy
Sheets("12 New Totals").Select
Range("B92").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

so ... since i had sent out 36 of these already, i was hoping i could
send them an empty spreadsheet with one simple macro that they could
then call, from within a given hospital-specific spreadsheet, such
that it would change the above VBA macro to read:

Sub CopyFinalRevsToNewTotals()
'
' Copy Revs from CY13 Final to 12 New Totlas
'
'
Sheets("CY13 Final").Select
Range("B8:X19").Select
Selection.Copy
Sheets("12 New Totals").Select
Range("B92").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

as there's no way in #33lLthey'd want to open the VBA editor and do
their own search and replace (trust me, they'd balk AND i've got the
VBA locked down since it's all my proprietary code)

note: in the above it is only CY12 FInal that has changed to CY13
Final ... the "12 New Totals" refers to Page 12 of the financial
report

anyway - as mentioned in my original post, in the past, i've done
something similar (i.e., sned them empty spreadhseet w/ "fix" macors
they could run frm their end) but only to change (e.g.,) formulas in
misc. tabs, etc. ... never to change the underlying VBA code

</verbose off :-)

thanks again

over & out

Thanks for the detailed reply. While I seem to be inclined to suggest
your users simply right-click your button and assign a macro to it,
this would depend if the macro already exists AND what type of button
it is.

Arbitrarily, I see your project as being a good candidate for an addin
whereby you can release updates AND the workbook is reusable in that
you provide it as a template. This will give you 2 distinct advantages:
Your code is independant of the fiscal working file; your working file
template can be revised independant of the code. When both get revised
you simply release an update 'zip' to replace existing components.

Replacing the template in no way affects any current working files.
Converting the project to an addin allows you to do all sorts of things
to customize how users use your project, including implimenting its own
menus/toolbars which can be configured to be context-sensitive as per
the active sheet required functionality.

I've done several similar projects in the past. Some even support their
own 'addins' (that means addins that also have addins) for
user-specific 'plugin' functionality. This includes stuff for my local
hospital as well as educational institutions. I'd be willing to review
your project if you upload a copy to a public share site and post back
a download link. Optionally, I can contact you by email if you post
your address (in a cryptic format so it has to be visually converted).

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 06/01/2015 20:46, William W. Viergever wrote:
On Mon, 05 Jan 2015 23:52:22 -0500, GS wrote:

What do you mean when you say "new tab names"? If you're referring to
worksheet tabs then your project needs a serious overhaul if it doesn't
work because a user renamed a sheet! OR do you intend to distribute a
'bandaid' for every user who renames a sheet?

Better (IMO) to properly rebuild your project!!!

hi grary - thanks for the reply

i guess i was trying to be too parsimonious; sorry

<verbose on :-)

these hospital sepcific spreadsheets contains about 30 tabs of
finacial data based on a report the hospitals submit to the State ..
they have to submit this reports 45 days after fiscal year end ...
what we do is, a year or so later, take Medicaid paid claims (and AR
and denied days data from the hospitals) to get an exact Medicaid Net
Revenue (which resides on yet another, non-State-report, "tab") and
uise that to "redistribute" everything (based on existing ratios,
etc.) not only across all the Medicaid financial categories, but also
across the different payors (e.g., Mediicare, Commerical Inusrance,
Self-Pay, etc., such that Total Net Revenues, i.e., across ALL
payores, remains the same) ... and then we re-subnmit this "revised"
report to the State (note: this State mandated financial report is
used to determine qualification for a Mediciad "Disproportionate
Share" supplemental reimbursement pool (about 140-150 out of 450+
Calif. hospitals typically qualify for about $1.1B in supp revnue)
.... so, in my hosp-secific spreadsheets, think of all those tabs as
original and final ... and i send these off to a couple of other
consultants (who do the in-hospital data pulling, and final assembling
of all data)

as such, there's all sorts of "buttons" that do certain things to make
life easier for these other guys

and for one of those buttons' code, i forgot to update it to reflect
the new (fiscal year) tab name, CY13 Final:


Sub CopyFinalRevsToNewTotals()
'
' Copy Revs from CY12 Final to 12 New Totlas
'
'
Sheets("CY12 Final").Select
Range("B8:X19").Select
Selection.Copy
Sheets("12 New Totals").Select
Range("B92").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

so ... since i had sent out 36 of these already, i was hoping i could
send them an empty spreadsheet with one simple macro that they could
then call, from within a given hospital-specific spreadsheet, such
that it would change the above VBA macro to read:

Sub CopyFinalRevsToNewTotals()
'
' Copy Revs from CY13 Final to 12 New Totlas
'
'
Sheets("CY13 Final").Select
Range("B8:X19").Select
Selection.Copy
Sheets("12 New Totals").Select
Range("B92").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

as there's no way in #33lLthey'd want to open the VBA editor and do
their own search and replace (trust me, they'd balk AND i've got the
VBA locked down since it's all my proprietary code)

note: in the above it is only CY12 FInal that has changed to CY13
Final ... the "12 New Totals" refers to Page 12 of the financial
report

anyway - as mentioned in my original post, in the past, i've done
something similar (i.e., sned them empty spreadhseet w/ "fix" macors
they could run frm their end) but only to change (e.g.,) formulas in
misc. tabs, etc. ... never to change the underlying VBA code

</verbose off :-)

thanks again

over & out


One way to do this is to isolate the things that can change regularly
like the year number and build the filenames and headings up from that.
If you only have to make a single change of year number then the scope
for parts getting out of sync is much reduced.

Why can't you just send them a corrected version?

Or a patch file to alter just the latest differences (old school method
from the days when updates had to go down a dialup modem).

You can use VBA in one project to alter the code in another or to add
code to a newly created workbook but you would need to test it very
carefully to make sure it does exactly what you want.

--
Regards,
Martin Brown

One way to do this is to isolate the things that can change regularly
like the year number and build the filenames and headings up from
that. If you only have to make a single change of year number then
the scope for parts getting out of sync is much reduced.

Why can't you just send them a corrected version?

Or a patch file to alter just the latest differences (old school
method from the days when updates had to go down a dialup modem).

You can use VBA in one project to alter the code in another or to add
code to a newly created workbook but you would need to test it very
carefully to make sure it does exactly what you want.

This is precisely why I recommend converting the project to an addin
and using a template workbook for subsequent years. Both are, as you
would well know, easily maintainable for updates/revisions over time!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion