Self Taught (badly) VBA Help :(

[Grammarjunkie]

I know Excel formulas, and I assumed VBA would be the same. It has similarities, but it definitely is its own beast. I was hoping someone could take a look at my VBA code, and just use one little piece as an example as to how to write it ... prettier? I know that's a tall order. But I know the way I have it written can't be the easiest, simplest way.

And also it causes some weird things. Like pressing Enter, instead of it going to the next cell (that in my VBA code is ".Activate"), it jumps to a random one. In one instance it jumped to the cell BEFORE it. I don't know how I accomplished that.

On any account, I'm not asking for anyone to rewrite literally all of it. (Though that would make my day.)

Just a little "VBA for Dummies." As I said, I'm self taught, so I just played around until I got it to do what I wanted. But it's probably not written the most ... logically.

Thanks for even considering it. I appreciate it so much.

The following is my code (it's not that long compared to what y'all do, I bet):

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Unprotect
Application.ScreenUpdating = False

If Range("F45") = "Y" Then
    Rows("46").Hidden = False
    Range("F46").Activate
Else
    Rows("46").Hidden = True
End If

If Range("F45") = "y" Then
    Rows("46").Hidden = False
    Range("F46").Activate
Else
    Rows("46").Hidden = True
End If

If Range("F46") = "Y" Then
    Rows("47").Hidden = False
    Range("F47").Activate
Else
    Rows("47").Hidden = True
End If

If Range("F46") = "y" Then
    Rows("47").Hidden = False
    Range("F47").Activate
Else
    Rows("47").Hidden = True
End If


If Range("J3") = "TPO" Then
    Rows("14:15").Hidden = False
Else
    Rows("14:15").Hidden = True
End If

If Range("D13") = "Purchase" Then
    Rows("86").Hidden = False
Else
    Rows("86").Hidden = True
End If

If Range("F102") = "Y" Then
    Rows("103").Hidden = False
    Range("F103").Activate
Else
    Rows("103").Hidden = True
End If

If Range("F102") = "y" Then
    Rows("103").Hidden = False
    Range("F103").Activate
Else
    Rows("103").Hidden = True
End If

If (Range("D6") <> "") And (Range("E18") <> "") And (Range("D6") > Range("E18")) Then
        Rows("39:41").Hidden = False
Else
        Rows("39:41").Hidden = True
End If


If Range("E19") = "" Then
        Rows("48:52").Hidden = True
Else
        Rows("48:52").Hidden = False
End If

If Range("D13") = "Purchase" Then
    Rows("60").Hidden = False
Else
    Rows("60").Hidden = True
End If

If Range("E22") = "" Then
    Rows("53:57").Hidden = True
Else
    Rows("53:57").Hidden = False
End If

If Range("D13") = "Purchase" Then
    Rows("105:114").Hidden = False
Else
    Rows("105:114").Hidden = True
End If

If Range("J3") = "TPO" Then
    Rows("119:122").Hidden = False
Else
    Rows("119:122").Hidden = True
End If

If Range("D13") = "Purchase" Then
    Rows("128:131").Hidden = False
Else
    Rows("128:131").Hidden = True
End If

If Range("J3") = "TPO" Then
    Rows("127").Hidden = False
Else
    Rows("127").Hidden = True
End If

If Range("F96") = "Y" Then
    Rows("97:98").Hidden = False
    Range("F97").Activate
Else
    Rows("97:98").Hidden = True
End If

If Range("F96") = "y" Then
    Rows("97:98").Hidden = False
    Range("F97").Activate
Else
    Rows("97:98").Hidden = True
End If

If Range("F90") = "Y" Then
    Rows("91:96").Hidden = False
    Range("F91").Activate
Else
    Rows("91:96").Hidden = True
End If

If Range("F90") = "y" Then
    Rows("91:96").Hidden = False
    Range("F91").Activate
Else
    Rows("91:96").Hidden = True
End If

If Range("F102") = "Y" Then
    Rows("103").Hidden = False
    Range("F103").Activate
Else
    Rows("103").Hidden = True
End If

If Range("F102") = "y" Then
    Rows("103").Hidden = False
    Range("F103").Activate
Else
    Rows("103").Hidden = True
End If

If Range("F107") = "Y" Then
    Rows("108:109").Hidden = False
    Range("F108").Activate
Else
    Rows("108:109").Hidden = True
End If

If Range("F107") = "y" Then
    Rows("108:109").Hidden = False
    Range("F108").Activate
Else
    Rows("108:109").Hidden = True
End If


Application.ScreenUpdating = True
ActiveSheet.Protect


End Sub

 

[Grammarjunkie]

Oh my gosh, we have been looking at this for so long that we were overlooking the obvious.
What does the UCase function do? Converts it to ALL CAPS.

So UCase(Target) can NEVER equal "Purchase".
It CAN equal "PURCHASE".

So your line of code has to be:

            If UCase(Target) = "PURCHASE" Then

I actually was wondering how UCase(Target) equates to "a lower case or upper case 'y' can be typed in here and it works."

So UCase(Target) changes whatever is in the field to uppercase, and then it's reading if it reads "Y", reveal these rows.

So I had to say UCase(Target) = "PURCHASE" so that it all caps purchase in the field and then they equal each other.

And that's why the UCase(Target) = "TPO" worked. Because TPO is an acronym and was always in all caps.

Am I understanding that right?

 

[Joe4]

Precisely.

 

[Grammarjunkie]

Precisely.

Seeeeeee?! I get stuff! Haha. Thanks for all your help, Joe. You are the MVP!