LinkBack URL
About LinkBacks
Hi
To keep this really simple I need a macro that will do the following.
If Sheet1!A1 =1 then hide the following rows Sheet2!123.
Thanks in advance.
Dan
Hi
To keep this really simple I need a macro that will do the following.
If Sheet1!A1 =1 then hide the following rows Sheet2!123.
Thanks in advance.
Dan
Nothing is simple in case of VBA..
try this..
Need to place in Sheet Module.
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Application.DisplayAlerts = False If Target.Address = "$A$1" Then On Error GoTo Deb Sheets("Sheet2").Range("1:3").EntireRow.Hidden = Target.Value = 1 End If Deb: Application.EnableEvents = True Application.DisplayAlerts = True End Sub
PS: Slightly Changed
Last edited by Debraj Roy; 11-24-2014 at 12:33 PM.
Regards!
=DEC2HEX(3563)
If you like someone's answer, click the star to give them a reputation point for that answer...
Hello dwilkinson12,
You may try;
Regards.Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Range("A1").Value = 1 Then Sheet2.Range("A123").Rows.EntireRow.Hidden = True End If End Sub
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
Maybe:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then If Target.Value = 1 Then Sheets("Sheet2").Rows("1:3").Hidden = True Else Sheets("Sheet2").Rows("1:3").Hidden = False End If End If End Sub
Everytime I run all 3 of these it wants me to name the macro and that isn't working as it just creates a blank piece of vb code.
Dan
Dan, are you putting the code in a standard module? If yes, then that is your mistake. These are sheet-level code that are triggered by events in the sheet. The code goes in the sheet code, not a standard module and you don't run it in the vba window. You run it by making a change in the worksheet.
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Check the attached..
EF1051546.xlsm
Hi I downloaded the attachment and ran the macro but it's doing the same. Everytime I click play/run in VB Editor it just pops up with a new macro box.
Thanks,
Dan
Did you tried.. by changing cell "A1" ..
Tested this code and works fine:
Put in Sheet1's code:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Sheets("Sheet2").Range("1:3").EntireRow.Hidden = Target.Value = 1 End Sub
When I paste into vb or even just open up the document that was attached above. It is within the sheet tab. I click on run macro and it pops up with the same box that would appear if I was trying to assign a macro to a button. I don't think excel is picking up on the fact there is a macro there. I think it has something to do with the name as the macro doesn't appear in my macros list even if i change it from Private Sub to just Sub? Anymore suggestions?
Macros are definitely enabled.
Cheers,
Dan
Hi Dan,
This macro is a "Event Driven" code.. you dont have to pres / click anything to get it fired.
Simply change the value in Sheet 1.. and it will reflect its work..
Hello dwilkinson12,
I think we are now at a stage where we would want to see what your Workbook looks like.
Please attached a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Dan:
Look here:
http://www.contextures.com/xlvba01.html
Refer to the section Copy Excel VBA Code to a Worksheet Module. All of the codes suggested above should work for you.
Sorry about this guys I was being stupid, Debraj Roy thankyou didn't realise that the macro didn't have to be manually ran.
Thanks,
Dan
I have incorporated this into my master sheet and included this code in a bigger macro.
Yesterday I was able to run a copy and paste special (values) macro to update the reference cell and then your macro would work and hide various rows.
However today your macro will not update or hide the cells unless I go to that reference cell manually and change the number. Any ideas?
Cheers,
Dan
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks