Disable marco based on the drop down selection.

BMD44

Board Regular
Joined
Sep 25, 2019
Messages
72
Hello,

I have a requirement to create a pivot table in excel if user selects 'Yes' in a drop down which is in cell C2.

I have a macro 'Pivot' which creates pivot table. I am looking for an option to disable the macro by default and if the value in drop down is 'No'. The 'Pivot' macro should be enabled only when the user selects 'Yes' in the drop down.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
I have to wonder why you don't just run code based on the combo selection. Not sure what you mean by disable by default; it's not something you do.
If the pivot procedure and the combo procedure (I presume it's the combo click event) are in the same sheet module, just check what the combo selection is before running the pivot code. So you aren't disabling, you're handling code based on conditions.
 
Upvote 0
Hello,

Thanks for the reply. I meant by disabling (greying) the Button so that user cannot click the button and create pivot table if the selection is 'Yes'.

To your point, you mean to say to have a condition in the pivot table code to run only when the drop down is 'Yes'.

If so, can you suggest how can I do it. Also, the code creates a new pivot table in a new sheet. The drop down for 'Yes/No' and the pivot table are in 2 different sheets.

VBA Code:
Sub InsertPivotTable()


'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("Source")


'Define Data Range
LastRow = DSheet.Cells(9999, "C").End(xlUp).row
'LastRow = DSheet.Cells(Rows.Count, "C").End(xlUp).row
LastCol = DSheet.Cells(9, Columns.Count).End(xlToLeft).Column
'LastCol = DSheet.Cells(9, S).End(xlToLeft).Column
Set PRange = DSheet.Cells(9, "C").Resize(LastRow - 8, LastCol - 3)
 
Upvote 0
I meant if the user chooses Yes from the combo, then just run the pivot code using the combo (change?) event.
If you like the idea of having to choose yes and still click a button, then go to the property sheet for the button and set enabled property to No. That way, whenever the workbook opens, it will be as designed. Then when the user selects Yes from the combo, set the command button enabled property to yes so they can click it to create the pivot. Question is, after the pivot code runs, do you disable the button again or leave it as is?

If you want to refer to the combobox from pivot code, then something like
VBA Code:
If Sheets("Source").comboboxNameGoesHere <> "Yes" Then Exit Sub
On Error Resume Next
Application.DisplayAlerts = False
etc.
however it's sort of sloppy to initiate code when you don't have to (that is, if you keep the command button idea).

These 2 statements are mutually exclusive so can't tell if pivot code is supposed to do its thing if yes or not.
create a pivot table in excel if user selects 'Yes'
user cannot click the button and create pivot table if the selection is 'Yes'.
 
Upvote 0
Thanks again.

Can I refer excel cell range here. Below statement gives error

If Sheets("Source").$C$2 <> "Yes" Then Exit Sub
 
Last edited:
Upvote 0
Try
If Sheets("Source").Range("C2") <> "Yes" Then Exit Sub
Sometimes I find such references are case sensitive but I haven't figured out why. So watch for yes vs YES vs Yes. If that turns out to be a problem, post back.
 
Upvote 1
Solution
Try
If Sheets("Source").Range("C2") <> "Yes" Then Exit Sub
Sometimes I find such references are case sensitive but I haven't figured out why. So watch for yes vs YES vs Yes. If that turns out to be a problem, post back.
Hello, Thank you. This works.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top