conditional formatting and command button

kath_excel

New Member
Joined
Dec 2, 2016
Messages
8
Hello, I have a command button on Sheet 1 which when clicked will ask for a password to unhide Sheet 2. Now on Sheet 1, I have few cells that I have made mandatory to other users to complete. There is conditional formatting with background red color for those mandatory to complete cell. When empty, they are red and if text entered then background disappears. Now, is there any way, that the command button can refuse to unhide Sheet 2 unless all the mandatory fields with red background are complete. It will only unlock when it does not find any conditional formatting present on Sheet 1????
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
You can add a few lines to your command button code that will check to see if the mandatory cells are filled properly before unhiding sheet2. If you post your button code and explain which cells (cell addresses) are mandatory and what their content should be, someone can help you with this.
 
Upvote 0
Private Sub CommandButton1_Click()
'unhide sheet2'
ActiveWorkbook.UNPROTECT "opensheet"
Dim wrksht As Worksheet
Dim Password As String
Password = "opensheet"
If InputBox("Enter password to continue.", "Enter Password") <> Password Then
Exit Sub
End If
For Each wrksht In ActiveWorkbook.Worksheets
Sheet2.Visible = xlSheetVisible
Next wrksht
ActiveWorkbook.PROTECT "opensheet"
ActiveSheet.PROTECT "opensheet", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

This is the code to unhide sheet2. There are few cells on sheet1 that hv conditional formatting when they are empty. Shows background red color when empty and no background when there is data in there.

Now the change that i want to do is that the command button will only be visible when there are no cells with conditional formatting which means the user has entered data in all the required cells. How can i do that???
 
Upvote 0
This is the code to unhide sheet2. There are few cells on sheet1 that hv conditional formatting when they are empty. Shows background red color when empty and no background when there is data in there.

Now the change that i want to do is that the command button will only be visible when there are no cells with conditional formatting which means the user has entered data in all the required cells. How can i do that???
What are the addresses of the few cells on Sheet1?
 
Upvote 0
Hi joe, sheet1 has a drop down. For every item in the drop down there is a separate range of cells displayed and hidden. There is a separate macro for every item in the drop down which hides and unhides the cell ranges. Do you think it will be more easy to add something to each item's macro??
So, if item A selected from the dropdown on sheet1 and cells A1, B10, D20 do not show any conditional formatting (red background) then make the command clickable Or Else give a messages, please complete the required cells.

Same for item B selected in the drop down with formatting in cells E5, I10 and M8.
Can you help with the code???
 
Upvote 0
Hi joe, sheet1 has a drop down. For every item in the drop down there is a separate range of cells displayed and hidden. There is a separate macro for every item in the drop down which hides and unhides the cell ranges. Do you think it will be more easy to add something to each item's macro??
So, if item A selected from the dropdown on sheet1 and cells A1, B10, D20 do not show any conditional formatting (red background) then make the command clickable Or Else give a messages, please complete the required cells.

Same for item B selected in the drop down with formatting in cells E5, I10 and M8.
Can you help with the code???
I can help, but only if you provide all the needed information.

1. What are the items in the drop down?
2. Are the separate macros triggered by changing the drop down, or are they run manually? If they are triggered, post the event code that runs.
2. For each item, what is the name of the macro that goes with the item, and which cells have to be filled before the command button should be made visible?
 
Upvote 0
Hello Joe,
1. I sent you in the message
2. Yes, there are separate macros triggered. sent you event code in the message
3. sent you in the message.

Please let me know if you need anything else.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

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