[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

public property in one workbook, want to set value from another

Mark VII

12/14/2006 9:22:00 PM

I have a utility program contained in one Excel workbook that opens and
modifies another workbook. I'd like the utility program to be able to set
the value of a variable in the second workbook.

To put this in context, when the utility program makes certain changes, it
fires the Worksheet Change event in the in the second workbook. This is
creating havoc with performance. I want to be able to add a logic branch to
the Worksheet Change sub to bypass its logic under these circumstances.

I've created a public property in the second workbook, but where I'm
stumbling is how to build the right object references so the utility program
can set the property. Any suggestions?

TIA...
Mark
5 Answers

Tim Williams

12/14/2006 9:26:00 PM

0

Have you tried setting Application.Calculation to manual, making the changes and then setting it back to automatic?

This would not require a flag in the second workbook.

--
Tim Williams
Palo Alto, CA


"Mark VII" <MarkVII@discussions.microsoft.com> wrote in message news:AD733C38-4925-430D-9825-44BEF63CD880@microsoft.com...
> I have a utility program contained in one Excel workbook that opens and
> modifies another workbook. I'd like the utility program to be able to set
> the value of a variable in the second workbook.
>
> To put this in context, when the utility program makes certain changes, it
> fires the Worksheet Change event in the in the second workbook. This is
> creating havoc with performance. I want to be able to add a logic branch to
> the Worksheet Change sub to bypass its logic under these circumstances.
>
> I've created a public property in the second workbook, but where I'm
> stumbling is how to build the right object references so the utility program
> can set the property. Any suggestions?
>
> TIA...
> Mark

NickHK

12/15/2006 1:56:00 AM

0

Mark,
What about toggling Application.EnableEvents=False/true ?

NickHK

"Mark VII" <MarkVII@discussions.microsoft.com> wrote in message
news:7A7A420E-0323-4E48-A25E-D6D5271D60E1@microsoft.com...
> > Have you tried setting Application.Calculation to manual, making the
> changes and then setting it back to automatic?
>
> Interestingly, I tried to do exactly that. Unfortunately, the Worksheet
> Change event in the second workbook contains logic where I'm turning
> calculation off, then on again as part of some special handling of user
> updates. Consequently, calcuation starts out turned off by the utility
> program, but the second workbook's Worksheet Change logic turns it back on
at
> the end of the sub.
>
> Mark

Tim Williams

12/15/2006 2:19:00 AM

0

.... or only setting calculation back to automatic if it was initally set that way?

In your worksheet_change event handler:

dim bAuto

If application.calculation = xlautomatic
bAuto= true
application .calculation = xlmanual
end if

'.....do stuff

'set back to auto only if required
if bAuto then application.calculation = xlautomatic



--
Tim Williams
Palo Alto, CA


"NickHK" <TungCheWah@Invalid.com> wrote in message news:uLLDxw%23HHHA.3780@TK2MSFTNGP02.phx.gbl...
> Mark,
> What about toggling Application.EnableEvents=False/true ?
>
> NickHK
>
> "Mark VII" <MarkVII@discussions.microsoft.com> wrote in message
> news:7A7A420E-0323-4E48-A25E-D6D5271D60E1@microsoft.com...
> > > Have you tried setting Application.Calculation to manual, making the
> > changes and then setting it back to automatic?
> >
> > Interestingly, I tried to do exactly that. Unfortunately, the Worksheet
> > Change event in the second workbook contains logic where I'm turning
> > calculation off, then on again as part of some special handling of user
> > updates. Consequently, calcuation starts out turned off by the utility
> > program, but the second workbook's Worksheet Change logic turns it back on
> at
> > the end of the sub.
> >
> > Mark
>
>

Tim Williams

12/18/2006 8:05:00 PM

0

The best way to do that might be to create a public sub in the workbook and call that to set the property.

dim b_setting as boolean

Public Sub MyValue(bValue as boolean)
b_setting = bValue
end sub

or add property Get/Let routines to the workbook code module.



--
Tim Williams
Palo Alto, CA


"Mark VII" <MarkVII@discussions.microsoft.com> wrote in message news:C68E2A56-88FD-444C-8634-2425C9A6DFEE@microsoft.com...
> Thanks for that suggestion. It helped a lot, but it would be nice to be able
> to manipulate a property in one workbook from another workbook's code.
>
> Any suggestions?
>
> "Tim Williams" wrote:
>
> > .... or only setting calculation back to automatic if it was initally set that way?
> >
>

Tim Williams

12/19/2006 4:37:00 AM

0

A simple approach would be to define your variable in the "ThisWorkbook"
module:

Public s_Value As String

In the "controlling workbook:

Sub Tester()

Dim wb
Set wb = Application.Workbooks("Property1.xls")
wb.s_Value = "blah"
MsgBox wb.s_Value

End Sub

or show the code you have already.

Tim



"Mark VII" <MarkVII@discussions.microsoft.com> wrote in message
news:4884269D-516A-4513-913E-203956CB6F6F@microsoft.com...
> Hi Tim --
>
> I've got the property GET and LET routines built in a class module of
> workbook #2. Where I'm stumbling is the object references to manipulate
> these
> values from VBA that gets invoked in workbook #1.
>
> Thanks,
> Mark
>
>> The best way to do that might be to create a public sub in the workbook
>> and call that to set the property.
>