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
I'm at a loss. The code as it is doesn't hide or unhide anything no matter if I add to the cell or take from the cell.
Click to expand...
Please post your current version of your code in its entirety. I want to make sure that you are shutting off events or have some accidental nesting going on.

BTW, you do not need to use UCASE if you are checking for a blank entry.
It is important to understand what it is being used for. Let's say that a user might be entering the word "Yes" in one of three different ways: "Yes", "yes", or "YES".
And let's say that you want to handle them all the same in your code. You could right three different IF THEN or CASE statements.
Or, you could simply tell VBA to convert the entire entry to Upper Case. Then all three of those entries would return "YES", and you can do a single check on it in VBA.
Likewise, you could have used LCASE and checked for "yes" instead.

So if you are checking for an entry with no letters in it (either a space, blank, or all numbers), there is no point in using UCASE or LCASE.
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Joe4 said:
Please post your current version of your code in its entirety. I want to make sure that you are shutting off events or have some accidental nesting going on.

BTW, you do not need to use UCASE if you are checking for a blank entry.
It is important to understand what it is being used for. Let's say that a user might be entering the word "Yes" in one of three different ways: "Yes", "yes", or "YES".
And let's say that you want to handle them all the same in your code. You could right three different IF THEN or CASE statements.
Or, you could simply tell VBA to convert the entire entry to Upper Case. Then all three of those entries would return "YES", and you can do a single check on it in VBA.
Likewise, you could have used LCASE and checked for "yes" instead.

So if you are checking for an entry with no letters in it (either a space, blank, or all numbers), there is no point in using UCASE or LCASE.
Click to expand...


This is what I have so far:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to make sure only one cell has been updated (otherwise exit)
    If Target.Count > 1 Then Exit Sub
    
    ActiveSheet.Unprotect
    Application.ScreenUpdating = False

'   Decide what to do based on which cell was updated
    Select Case Replace(Target.Address, "$", "")
    
'       Cell F45 or F46 or F102
        Case "F45", "F46", "F102"
            If UCase(Target) = "Y" Then
                Rows(Target.Row + 1).Hidden = False
            Else
                Rows(Target.Row + 1).Hidden = True
            End If
            
'       Cell J3
        Case "J3"
            If UCase(Target) = "TPO" Then
                Rows("14:15").Hidden = False
            Else
                Rows("14:15").Hidden = True
            End If
            
'       Cell F107
        Case "F107"
            If UCase(Target) = "Y" Then
                Rows("108:109").Hidden = False
            Else
                Rows("108:109").Hidden = True
            End If

'       Cell F90
        Case "F90"
            If UCase(Target) = "Y" Then
                Rows("91:96").Hidden = False
            Else
                Rows("91:96").Hidden = True
            End If
            
'       Cell F96
        Case "F96"
            If UCase(Target) = "Y" Then
                Rows("97:98").Hidden = False
            Else
                Rows("97:98").Hidden = True
            End If


'       Cell E22
        Case "E22"
            If UCase(Target) = "" Then
                Rows("53:57").Hidden = True
            Else
                Rows("53:57").Hidden = False
            End If
            
'       Cell E19
        Case "E19"
            If UCase(Target) = "" Then
                Rows("48:52").Hidden = True
            Else
                Rows("48:52").Hidden = False
            End If

End Select

 Application.ScreenUpdating = True
    ActiveSheet.Protect

End Sub


In the case of J3 reading "TPO," I have other rows that I need to hide also when it reads TPO, but I'm not sure how to write them in a series.
And then, as you noted, the blanks might not be working since I have it as UCase. I did understand what that was reading, but I guess I thought having it would be inconsequential if I'm saying if the field is blank. An erroneous assumption, it would seem. haha. I really appreciate you helping. I wouldn't be pestering if I wasn't so close. I have no need for VBA codes at all except for to hide rows.
 
Last edited:
Upvote 0
I did understand what that was reading, but I guess I thought having it would be inconsequential if I'm saying if the field is blank. An erroneous assumption, it would seem.
Click to expand...
I just tested it out, and it does work with the UCase on the blank lines. Note that I wasn't saying that it wouldn't, I was just telling you that it is not necessary. I wanted to make sure that you understand the logic of what we were doing when we were using it with the other text entries.

The code works for me. The only thing is after you make any update, the whole sheet is protected. I am take it that you have unlocked select cells so that the whole sheet isn't locked for editing.

So what exactly are you entering in cell E19 that it isn't working for you?
 
Upvote 0
Joe4 said:
I just tested it out, and it does work with the UCase on the blank lines. Note that I wasn't saying that it wouldn't, I was just telling you that it is not necessary. I wanted to make sure that you understand the logic of what we were doing when we were using it with the other text entries.

The code works for me. The only thing is after you make any update, the whole sheet is protected. I am take it that you have unlocked select cells so that the whole sheet isn't locked for editing.

So what exactly are you entering in cell E19 that it isn't working for you?
Click to expand...


So when you enter something into E19, and then delete it, Rows 48:52 Hide for you?

I don't get why they wouldn't for me.

What I'm trying to do here is have them be hidden, and if anything at all is typed into E19 (it will be dates), have them unhide.
By default, I'd like them hidden. Because it's rare that anything will be in E19.

In fact, none of these are hidden by default. Which is what I want. I guess I need to sign up for an online course or something. Haha.
 
Last edited:
Upvote 0
OK. I would recommend adding a Workbook_Open event to set your "defaults". This needs to be placed in the "ThisWorkbook" module.

If your are always starting with a blank template, you can just hide the rows right from the start like this:
Code: 
Private Sub Workbook_Open()

'   Select the sheet to apply this to
    Sheets("Sheet1").Activate
    
'   Hide rows
    Rows("48:52").Hidden = True
    
'   REST OF CODE BELOW...

End Sub
If your sheet may already have data in it (it is a sheet that you continually work on), you will need to add your conditions/checks, i.e.
Code: 
Private Sub Workbook_Open()

'   Select the sheet to apply this to
    Sheets("Sheet1").Activate
    
'   Hide rows
    If Range("E19") = "" Then
        Rows("48:52").Hidden = True
    End If
    
'   REST OF CODE BELOW...

End Sub
So the Workbook_Open event runs ONCE, every time the file is first opened, whereas the Worksheet_Change event runs whenever particular cells are updated.
 
Last edited:
Upvote 0
Joe4 said:
OK. I would recommend adding a Workbook_Open event to set your "defaults". This needs to be placed in the "ThisWorkbook" module.


So the Workbook_Open event runs ONCE, every time the file is first opened, whereas the Worksheet_Change event runs whenever particular cells are updated.
Click to expand...


AH! :D I got it. Working like a charm. I really appreciate your help! I wish I could pay you. I feel like you're doing a whole introductory course. But it's more than helpful, and I can't think you enough for your patience and time.

I hate to ask, but just a few brief questions:

1. I've tried every way my brain can think of, but I can't figure out how to do a series. For example:
Code: 
Cell J3
        Case "J3"
            If UCase(Target) = "TPO" Then
                Rows("14:15").Hidden = False
            Else
                Rows("14:15").Hidden = True
            End If

If J3 reads TPO, and I want more than 14:15 to unhide. I've attempted separating the rows in their own parenthesis, in the same parenthesis but different quotations, in the same parenthesis and in the same quotations. And none of it is working, but surely there's something easier than doing a whole new code...

Rows("14:15","16","22").Hidden = False. You get where my brain is going. How do I do a series of stuff to hide?

And 2, Lastly, if I wanted to type letters instead of use my dropdowns (hence the Ucase), and I wanted pressing Enter to accept my entry and take me to the next line that unhides, is the best way still to do

Code: 
Cell F88
        Case "F88"
            If UCase(Target) = "Y" Then
                Rows("89:91").Hidden = False
                Range("F89").Activate
            Else
                Rows("89:91").Hidden = True
            End If

That's what I was doing. So like instead of Enter taking me to line F92, it takes me to the newly unhidden row F89.

The only caveat with this and how I was doing it is that it was doing silly things. So I'm unsure this is actually the best way to handle that..


Those are my last two questions I swear. Grin and bear it please. Haha.
 
Last edited:
Upvote 0
Code: 
[COLOR=#333333]Rows("14:15","16","22").Hidden = False[/COLOR]
This one is not quite as intuitive, but it would look like this:
Code: 
Range("14:15, 16:16, 22:22").EntireRow.Hidden = True

So like instead of Enter taking me to line F92, it takes me to the newly unhidden row F89.
Click to expand...
If your goal is to have the cursor go to cell F92 after that part of the code runs, that is fine. I wasn't sure if you really wanted that to happen, or if it was just some leftover bit of recorded code you had.
I really appreciate your help! I wish I could pay you.
Click to expand...
No problem! I just tell people to "pay it forward!".
 
Upvote 0
Joe4 said:
Code: 
[COLOR=#333333]Rows("14:15","16","22").Hidden = False[/COLOR]
This one is not quite as intuitive, but it would look like this:
Code: 
Range("14:15, 16:16, 22:22").EntireRow.Hidden = True


If your goal is to have the cursor go to cell F92 after that part of the code runs, that is fine. I wasn't sure if you really wanted that to happen, or if it was just some leftover bit of recorded code you had.

No problem! I just tell people to "pay it forward!".
Click to expand...



I definitely pay it forward! I currently hold regular meetings to train people around the office in Excel formulas, if they have the want to know. :) It's fun. I like seeing their face light up when they get it, when something clicks. I imagine my face looks the same when I finally write my own little VBA thing or when I understand what you tell me. Haha.

May I ask what Compile error: Case without Select Case means? It looks the same as every other formula, but it's giving me an error, and I'm very unsure why it would be doing it.

Code: 
'       Cell F73
        Case "F73"
            If UCase(Target) = "Y" Then
                Rows("74:75").Hidden = False
            Else
                Rows("74:75").Hidden = True
            If UCase(Target) = "N" Then
                Rows("76:78").Hidden = False
            Else
                Rows("76:78").Hidden = True
            End If
                        
'       Cell F75
        Case "F75"
            If UCase(Target) = "N" Then
                Rows("76:78").Hidden = False
            Else
                Rows("76:78").Hidden = True
            End If

It's the bottom one, Cell F75, giving me the issue bug. I put the one before it as well in case I wrote it wrongly. The one above it is working, for sure. But I may have written it wrong and now the Cell F75 is giving me an error. Just an assumption.

Edit: Yeah, I definitely wrote it wrong. I'm gonna try some other ways and try to figure it out on my own. :)
 
Last edited:
Upvote 0
In your F73 block, you have two "IF" statements, but only one "END IF".
So you are unbalanced there.
 
Upvote 0
Joe4 said:
In your F73 block, you have two "IF" statements, but only one "END IF".
So you are unbalanced there.
Click to expand...

Code: 
 Cell F73
        Case "F73"
            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

Ah, dude! I see. I left out the End If because I figured since both were for the same F73, it didn't need it. Obvi a wrong assumption.
 
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,861
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