Prev: VB3 anyone...?
Next: Tabs
From: rookievestor on 16 Mar 2005 01:03
Hello everyone,

I have 2 questions:

1) Suppose I use a Macro to select a range of data for a Excel graph, and
convert it to my style. Then I want to add a number of checkbox, that if
clicked, show/hide certain linked dataseries. Since the data range is
different every time, I like dynamically create the checkboxes to match
number of dataseries.

For example, I have 10 series of data, 5 for lines (right scale), and 5 for
stacked bars (left scale). Then the program will auto-recognize the 10
series, and create 10 checkbox. If checkbox5 is checked, series will
appear/disppear from the chart. What's the right coding?

2) I am confused about range of code. Let's say I can write code in sheets,
say Sheet1, or workbook, or a module. What's difference between these 3
places? If I define in Sheet1:

Public/Private MyVariant as variant
Sub myroutine()
MyVariant = 2
End Sub

Is it going to run in other sheet of same workbook? How about I open a new
Excel sheet, does the code still work on new sheet? (What the scope?)

I am asking this because I like the graph code to work as a template. If
other user copy the sheet to other workbook, the thing is still working.

Can a userform be embedded to a worksheet?

Well, lot of questions, thank you very much for your expert opinion!!

Warren,


From: Steve Gerrard on 16 Mar 2005 02:44

"rookievestor" <rookievestor(a)hotmail.com> wrote in message
news:Q_PZd.12589$WK2.3428(a)newssvr30.news.prodigy.com...
>
> 1) Suppose I use a Macro to select a range of data for a Excel graph, and
> convert it to my style. Then I want to add a number of checkbox, that if
> clicked, show/hide certain linked dataseries. Since the data range is
> different every time, I like dynamically create the checkboxes to match number
> of dataseries.
>

VBA in Excel doesn't have a control array, so I think you would have to put 10
checkboxes on the form. You can show and hide them as needed, using the Visible
property. Another option might be to use a list box instead.

> 2) I am confused about range of code. Let's say I can write code in sheets,
> say Sheet1, or workbook, or a module. What's difference between these 3
> places? If I define in Sheet1:
>
> Public/Private MyVariant as variant
> Sub myroutine()
> MyVariant = 2
> End Sub
>
> Is it going to run in other sheet of same workbook? How about I open a new
> Excel sheet, does the code still work on new sheet? (What the scope?)
>
> I am asking this because I like the graph code to work as a template. If other
> user copy the sheet to other workbook, the thing is still working.
>
> Can a userform be embedded to a worksheet?
>

If you put code in a Sheet module, it will be copied when the sheet is copied.
This may be a problem if it is copied to the same file, creating duplicate
definitions of functions. Normally you would only put event handlers in a Sheet
module.

The same is generally true of putting code in a Workbook module. One or more
regular modules is the best place to put most code. AFAIK, user forms are added
to the file as separate modules (with their event handler code in them), and are
not associated with a specific worksheet. You can however put controls directly
on a worksheet.



From: rookievestor on 16 Mar 2005 23:25
Thank you Steve. Then is there a way to create a list of all dataseries and
output their names to excel?

Regarding pivot table, can I remove all formula/data from a pivot table by
code? For example, I want to add some metric to a pivot table based on
selection on a userform..first, I got to remove all existing data/formula
from the table. Also, do you know is it possible to change a pivot table's
name programmingly?

Regards,

"Steve Gerrard" <mynamehere(a)comcast.net> wrote in message
news:2K6dnXX9y4tGfqrfRVn-sA(a)comcast.com...
>
> "rookievestor" <rookievestor(a)hotmail.com> wrote in message
> news:Q_PZd.12589$WK2.3428(a)newssvr30.news.prodigy.com...
>>
>> 1) Suppose I use a Macro to select a range of data for a Excel graph, and
>> convert it to my style. Then I want to add a number of checkbox, that if
>> clicked, show/hide certain linked dataseries. Since the data range is
>> different every time, I like dynamically create the checkboxes to match
>> number of dataseries.
>>
>
> VBA in Excel doesn't have a control array, so I think you would have to
> put 10 checkboxes on the form. You can show and hide them as needed, using
> the Visible property. Another option might be to use a list box instead.
>
>> 2) I am confused about range of code. Let's say I can write code in
>> sheets, say Sheet1, or workbook, or a module. What's difference between
>> these 3 places? If I define in Sheet1:
>>
>> Public/Private MyVariant as variant
>> Sub myroutine()
>> MyVariant = 2
>> End Sub
>>
>> Is it going to run in other sheet of same workbook? How about I open a
>> new Excel sheet, does the code still work on new sheet? (What the scope?)
>>
>> I am asking this because I like the graph code to work as a template. If
>> other user copy the sheet to other workbook, the thing is still working.
>>
>> Can a userform be embedded to a worksheet?
>>
>
> If you put code in a Sheet module, it will be copied when the sheet is
> copied. This may be a problem if it is copied to the same file, creating
> duplicate definitions of functions. Normally you would only put event
> handlers in a Sheet module.
>
> The same is generally true of putting code in a Workbook module. One or
> more regular modules is the best place to put most code. AFAIK, user forms
> are added to the file as separate modules (with their event handler code
> in them), and are not associated with a specific worksheet. You can
> however put controls directly on a worksheet.
>
>
>


From: Steve Gerrard on 17 Mar 2005 00:09

"rookievestor" <rookievestor(a)hotmail.com> wrote in message
news:%E7_d.12835$WK2.12485(a)newssvr30.news.prodigy.com...
> Thank you Steve. Then is there a way to create a list of all dataseries and
> output their names to excel?
>
> Regarding pivot table, can I remove all formula/data from a pivot table by
> code? For example, I want to add some metric to a pivot table based on
> selection on a userform..first, I got to remove all existing data/formula from
> the table. Also, do you know is it possible to change a pivot table's name
> programmingly?
>

I'm afraid I don't know those answers. I believe you would find both dataseries
for charts, and the many properties of a pivot table, listed in the VBA Help for
Excel. There is a PivotTable class, for instance, so you could declare one and
start investigating its properties and methods.


 | 
Pages: 1
Prev: VB3 anyone...?
Next: Tabs