Command Button Performs Multiple Functions

[smhutch]

I have a work book with 18 work sheets. The first is a Main input sheet, the 2nd is a print friendly sheet and the other 16 are hidden. When certain products are selected on the Main sheet, the information is copy and pasted to the Print sheet (in white font).

I need a Command Button so that when the user is done on the Main sheet, they click the button and it reads the cells on the print sheet to unhide any of the other 16 sheets but only if the product was selected.

Example. Input sheet has Apples as a choice. The click on this and the word "apples" is pasted onto the Print sheet at A77. 8 total customers can have an input, so A77 through H77 can all have Apples in the cells.

Second product is Oranges, that wording is on the Print sheet A78 through H78.

When done, I need the command button to read the row for Apples, and if all cells have no data, then the Apples sheet stays hidden. If there is data, the Apples sheet is unhidden.

Then I need the same command button to apply the same principal to the oranges row.

I've tried the below, but can't get it to work.

Private Sub CommandButton10_Click()
If Sheets("Print").Range("A77, B77, C77, D77, E77, F77, G77, H77").Value = "" Then
Sheets("Apples").Visible = False
Else: Sheets("Apples").Visible = True
If Sheets("Print").Range("A78, B78, C78, D78, E78, F78, G78, H78").Value = "" Then
Sheets("Oranges").Visible = False
Else: Sheets("Oranges").Visible = True
End If
End If
End Sub

I can get it to work if i take off all of the 2nd If/Then Stmt.

 

[schielrn]

I don't think the way you are testing for "" is working how you think it is working. Maybe try this instead:

Private Sub CommandButton10_Click()
if worksheetfunction.CountBlank(Sheets("Print").Range("A77, B77, C77, D77, E77, F77, G77, H77"))=8 then
Sheets("Apples").Visible = False
Else: Sheets("Apples").Visible = True
if worksheetfunction.CountBlank(Sheets("Print").Range("A78, B78, C78, D78, E78, F78, G78, H78"))=8 Then
Sheets("Oranges").Visible = False
Else: Sheets("Oranges").Visible = True
End If
End If
End Sub

Hope that helps.

 

[smhutch]

I don't think the way you are testing for "" is working how you think it is working. Maybe try this instead:

Private Sub CommandButton10_Click()
if worksheetfunction.CountBlank(Sheets("Print").Range("A77, B77, C77, D77, E77, F77, G77, H77"))=8 then
Sheets("Apples").Visible = False
Else: Sheets("Apples").Visible = True
if worksheetfunction.CountBlank(Sheets("Print").Range("A78, B78, C78, D78, E78, F78, G78, H78"))=8 Then
Sheets("Oranges").Visible = False
Else: Sheets("Oranges").Visible = True
End If
End If
End Sub

Hope that helps.

I have a concern regarding the =8 part. Not all customer's may choose apples, therefore the tally may not equal 8. There may be only 3 cells in that entire row that have apples, and the apples sheet needs to unhide as long as there is just one of the word apples in that range of cells.

 

[schielrn]

It is saying if all 8 cells are blank then hide sheet apples? Isn't that what you want? If all 8 cells are not blank then sheet apples is visible?

 

[smhutch]

It is saying if all 8 cells are blank then hide sheet apples? Isn't that what you want? If all 8 cells are not blank then sheet apples is visible?

I looked at it wrong, my apologies.

I did try it but it has the following error
"Run-time error '1004'"
Unable to get the CountBlank property of the Worksheet Function class

 

[schielrn]

Maybre try:

Private Sub CommandButton10_Click()
if application.worksheetfunction.CountBlank(Sheets("Print").Range("A77, B77, C77, D77, E77, F77, G77, H77"))=8 then
Sheets("Apples").Visible = False
Else: Sheets("Apples").Visible = True
if application.worksheetfunction.CountBlank(Sheets("Print").Range("A78, B78, C78, D78, E78, F78, G78, H78"))=8 Then
Sheets("Oranges").Visible = False
Else: Sheets("Oranges").Visible = True
End If
End If
End Sub

 

[smhutch]

Still doesn't work, same error.

Let's try it this way. Please Note: I can get it to run the First If/Then statement and it works, I need it to run the first If/Then, and then check the next If/Then Statement.

There has to be something I'm missing to get it to run the first If/Then and then proceed to the next If/Then

Code:
Private Sub CommandButton10_Click()

If Sheets("Print").Range("A77, B77, C77, D77, E77, F77, G77, H77") = "Apples" Then
Sheets("Apples").Visible = True
End If
If Sheets("Print").Range("A78, B78, C78, D78, E78, F78, G78, H78") = "Oranges" Then
Sheets("Oranges").Visible = True
End If
End Sub

 

[smhutch]

RESOLVED

This is the code that eventually worked.

If Application.WorksheetFunction.CountIf(Sheets("Print").Range("A77:H77"), "Apples") > 0 Then<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Sheets("Apples").Visible = True<o></o>
End If<o></o>