vba code for after subtotal [2]

[LFKim2018]

after I subtotal a worksheet and clicked [2] at the upper left - the summary will be shown,
after which, I would like to deactivated the command button - how should the code be written?
kindly correct the following:

If ActiveSheet.Outline.ShowLevels RowLevels:=2 Then CommandButton1.Enabled = False
End If

many many thanks

 

[jproffer]

I'll bite....

Where are you clicking "2" at the upper left? What does that mean?

If you write your code exactly like that, you don't need the End If, by the way....so if it's giving an error, that may be why...but that's a shot in the dark on my part since I have no idea what "click [2] at the upper left" means.

 

[LFKim2018]

I'll bite....

Where are you clicking "2" at the upper left? What does that mean?

If you write your code exactly like that, you don't need the End If, by the way....so if it's giving an error, that may be why...but that's a shot in the dark on my part since I have no idea what "click [2] at the upper left" means.

Thank you for your reply..
I have a worksheet with data of > components, unit of measure and quantity - I have a vba code for a command button to sort it then subtotal it, after which the worksheet would show in the upper left corner > [1][2][3] to select from, the [2] will show the summary of the subtotaled data whereas [1] is the grand total & [3] is the general sheet - I would want to deactivate the command-button after [2] is selected.

the code below is incorrect with [ActiveSheet.Outline.ShowLevels RowLevels:=2] - how should this be written?
ActiveSheet.Outline.ShowLevels RowLevels:=2 is the vba for choosing [2]

If ActiveSheet.Outline.ShowLevels RowLevels:=2 Then
CommandButton1.Enabled = False
End If

Thank you

 

[jproffer]

Ok, I understand this part:

I have a worksheet with data of > components, unit of measure and quantity - I have a vba code for a command button to sort it then subtotal it

but then ya lost me at this part:

after which the worksheet would show in the upper left corner > [1][2][3] to select from, the [2] will show the summary of the subtotaled data whereas [1] is the grand total & [3] is the general sheet

What are 1, 2, and 3? More commandbuttons?

What kind of a worksheet is this? "Outline.Showlevels RowLevels"....not familiar with that, but I'm willing to try...just need some guidance myself on that part.

 

[LFKim2018]

Ok, I understand this part:



but then ya lost me at this part:



What are 1, 2, and 3? More commandbuttons?

What kind of a worksheet is this? "Outline.Showlevels RowLevels"....not familiar with that, but I'm willing to try...just need some guidance myself on that part.

When a worksheet of data is "sorted and subtotaled" - it will transform it into a sorted column and subtotaled at the end of each item - that worksheet will have that [1][2][3] located at the upper left of the worksheet

try this:
make a worksheet with column A = component, column B = unit of measure (UM), C = quantity (QTY)
then input several data - as follows
Column A B C
row1 Component UM QTY
row2 Round tube 1" pc 10
row3 Round tube 1" pc 5
Flatbar 1/2" pc 20
add some more ....

then hi-light column A2 down to the last row of column A (with data)
then at above ribbon click DATA > sort it alphabetically
after sorting, hi-light A1 down to end column C with data
then at DATA > do the "subtotal" on row C (QTY)
you will now have a worksheet with 3 small column at the left with [1][2][3] on top (upper left corner of the worksheet)
when you click [1] - the worksheet will show the grand total only
when you click [2] - the worksheet will show you the summary only
when you click [3] - the worksheet will show all sorted and subtotaled items

hope this clears something

anyway - pls don't bother anymore with the Outline.Showlevels RowLevels -- I changed the procedure and I don't need it anymore

thank you