Excel VBA

Hello Experts,

I need some guidance on how to get my VBA code to select the correct sheet.  I am not sure if it should be a "case select" or "If statement".  I have tried a few things, but the wrong sheet is being selected.

I have 5 sheets in the workbook and they all validate Range"I4"

I need it to determine which sheet to select based on the value of thiscell.

If sheet5.Range("I4").value = 0 then
Sheet4.activate

Elseif sheet4.Range("I4").value = 0 then
Sheet3.activate

Elseif  sheet3.Range("I4").value = 0 then
Sheet2.activate

Elseif sheet2.Range("I4").value = 0 then
Sheet1.activate

End if

I am not sure what I am doing wrong - but the code is not activating the correct sheet.  I always want it to eliminate sheet5, then sheet4, then sheet3, then sheet 2  and finally go to sheet1 if all the others are eliminated - in that sequence. Do I need to resort the Else statements or is this a Case issue?

mike637


mike637Asked:
Who is Participating?
 
CluskittConnect With a Mentor Commented:
You can only activate one at a time. Also, you're using ElseIF. If you want to check them all, you'll have to use separate Ifs. If <condition> Then means that it will test condition one. ElseIf <condition> Then means that, if the previous If was false, it will test this new If. If the previous was true, it will ignore.

If sheets(5).Range("I4").value = 0 then
Sheets(4).activate
End If
If sheets(4).Range("I4").value = 0 then
Sheets(3).activate
End If
If sheets(3).Range("I4").value = 0 then
Sheets(2).activate
End If
If sheets(2).Range("I4").value = 0 then
Sheets(1).activate
End if


Mind you, with this last one, only the last sheet will be active.
0
 
CluskittCommented:
If sheets(5).Range("I4").value = 0 then
Sheet4.activate

Elseif sheets(4).Range("I4").value = 0 then
Sheet3.activate

Elseif  sheets(3).Range("I4").value = 0 then
Sheet2.activate

Elseif sheets(2).Range("I4").value = 0 then
Sheet1.activate

End if
0
 
mike637Author Commented:

Forgot to add the last line in my original question:

    Else
    Sheet5.Activate

with the change of the code you supplied - I am getting sheet5 activated even though other sheets should be activated since they meet the criteria.

Can you offer additional help?
0
 
leonstrykerCommented:
All of your values are value = 0 !!!!!!
0
 
leonstrykerCommented:
Here is the code for the Select statement

Select Case Sheet5.Range("I4").Value
    Case 0
        sheet4.Activate
    Case 1
        Sheet3.Activate
    Case 2
        Sheet2.Activate
    Case 3
        Sheet1.Activate
    Case Else
        Sheet5.Activate
End Select
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.