Self Taught (badly) VBA Help :(

G

Grammarjunkie

Board Regular
Joined
Mar 22, 2016
Messages
86
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):

Code: 
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
 
Sort by date Sort by votes
Joe4 said:
In your F73 block, you have two "IF" statements, but only one "END IF".
So you are unbalanced there.
Click to expand...

Admittedly there's one more scenario I'm not quite writing right.

But frankly, I'm annoyed with myself asking you so many questions. So I'm going to hammer out this the best I can. Or try to.

Thank you so, so much for everything you've done. :) I can definitely replicate this, and that's the best part.
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Also note that in the construct of your F73 block, if you fix it, it will look like this:
Code: 
            If UCase(Target) = "Y" Then
                Rows("74:75").Hidden = False
            Else
                Rows("74:75").Hidden = True
            End If
            If UCase(Target) = "N" Then
                Rows("76:78").Hidden = False
            Else
                Rows("76:78").Hidden = True
            End If
But, if the value is "N", you will hit both the "Else" clause of the first IF (since it is not equal to "Y") AND the first part of the second IF (because it is equal to "N"). So if the value is N, you will be hiding rows 74:75 AND unhiding rows 76:78.

Not sure if that was your intention...
 
Upvote 0
Joe4 said:
Also note that in the construct of your F73 block, if you fix it, it will look like this:
Code: 
            If UCase(Target) = "Y" Then
                Rows("74:75").Hidden = False
            Else
                Rows("74:75").Hidden = True
            End If
            If UCase(Target) = "N" Then
                Rows("76:78").Hidden = False
            Else
                Rows("76:78").Hidden = True
            End If
But, if the value is "N", you will hit both the "Else" clause of the first IF (since it is not equal to "Y") AND the first part of the second IF (because it is equal to "N"). So if the value is N, you will be hiding rows 74:75 AND unhiding rows 76:78.

Not sure if that was your intention...
Click to expand...

That was my intention. :) I needed one answer to hide and unhide rows. I got that one working.

Edit: You seem like a nice guy. I'm going to take advantage of your kindness.

Code: 
Cell D6 And E18
        Case "D6", "E18"
            If (UCase("D6")) <> (UCase("E18")) Then
                Rows("39:41").Hidden = False
            Else
                Rows("39:41").Hidden = True
            End If

This looks very wrong. But I've tried it in every format I can think of. No parenthesis, no quotations, no outter parenthesis, etc. What's the correct format? I've thrown in the towel.
 
Last edited:
Upvote 0
If you want to compare the value in D6 to the value in E18, then use:
Code: 
If (UCase(Range("D6"))) <> (UCase(Range("E18"))) Then
Remember, values in quotes are considered literal string values.
The literal string "D6" will never be equal to the literal string "E18".
But you do not want the literal strings "D6" and "E18". You want the values from those ranges, so you need to use Range().
 
Upvote 0
Joe4 said:
If you want to compare the value in D6 to the value in E18, then use:
Code: 
If (UCase(Range("D6"))) <> (UCase(Range("E18"))) Then
Remember, values in quotes are considered literal string values.
The literal string "D6" will never be equal to the literal string "E18".
But you do not want the literal strings "D6" and "E18". You want the values from those ranges, so you need to use Range().
Click to expand...


Oh! My mind was interpreting "Range" to mean something else entirely. I see now. Since we are comparing the values in the cells, not the literal cells.

With that said, is that what's wrong with this?

Code: 
'       Cell E19
        Case "E19"
            If UCase(Target) <> "" Then
                Rows("48:51").Hidden = False
            Else
                Rows("48:51").Hidden = True
            End If

We talked about this one much earlier.. I set up all the rows I wanted to hide in the Workbook_Open. And everything is working. When something is typed into E19, rows 48:51 unhide. Hooray!

But... the perfectionist/little bit of OCD I have in me is annoyed that if the value from E19 is deleted, the rows don't hide again. Even though the code, to me, reads if it's blank for them to hide.
 
Last edited:
Upvote 0
But... the perfectionist/little bit of OCD I have in me is annoyed that if the value from E19 is deleted, the rows don't hide again. Even though the code, to me, reads if it's blank for them to hide.
Click to expand...
It seems to work fine for me.

How exactly are you deleting the value in E19?
Please let me know your exact key strokes.
 
Upvote 0
Joe4 said:
It seems to work fine for me.

How exactly are you deleting the value in E19?
Please let me know your exact key strokes.
Click to expand...

So say I enter 02/15/2016, press enter, then I scroll down to see the rows unhidden, then I scroll back up, select the cell, and press delete on the keyboard. Scroll down, and the rows are still unhidden. :confused:
 
Upvote 0
So say I enter 02/15/2016, press enter, then I scroll down to see the rows unhidden, then I scroll back up, select the cell, and press delete on the keyboard. Scroll down, and the rows are still unhidden.
Click to expand...
Strange...
If I perform those exact same steps, the rows hide for me.
It isn't a merged cell, is it?

Try modifying this section of code and see what it returns:
Code: 
        Case "E19"
            MsgBox "Length of E19 is " & Len(Range("E19"))
            If Len(Target) > 0 Then
                Rows("48:51").Hidden = False
            Else
                Rows("48:51").Hidden = True
            End If
 
Upvote 0
Joe4 said:
Strange...
If I perform those exact same steps, the rows hide for me.
It isn't a merged cell, is it?
Click to expand...

It IS merged. Sorry! I didn't think that was relevant. Haha. It returns the length of E19 is 10.

I wonder if that's my issue with two of the ones I'm having. They all involve a merged cell, come to think of it.

The ones I'm trying to write on my own that I have issues with involve E19 and E22, both of which are the same size and merged. (Not into each other, though. E19 and F19 are merged and E22 and F22)
 
Last edited:
Upvote 0
It IS merged. Sorry! I didn't think that was relevant. Haha. It returns the length of E19 is 10.
Click to expand...
Aha! The most important think you should take away from this thread is the following:
Merged cells are very, very bad, and you should avoid using them whenever possible!
I am serious. They wreak havoc with things like VBA and sorting. Most serious programmers will not touch them with a ten foot pole!

The good news is you can easily get the exact same visual effect that merged cells gives you without all the problems by using the "Center Across Selection" formatting option. So I would highly recommend removing all of your merged cells and use this format option instead. That should solve your issue.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Copy and unfilter in VBA
Replies
1
Views
173
Alex Blakenburg
A
J
  • Question Question
Combining VBA code together...need help!
Replies
3
Views
378
Fluff
Fluff
Rob_010101
  • Question Question
Mail Merge Macro - Saves to PDF/Word
Replies
1
Views
704
Rob_010101
Rob_010101
R
  • Solved
VBA Timer
2
Replies
11
Views
1K
Rana Gray
R
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
325
STARL0RD
S

Forum statistics

Threads
1,223,060
Messages
6,169,860
Members
452,286
Latest member
noclue2000

Share this page

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