Can someone help me please
I want to run a macro when cell b2 is = to “yes” in worksheet called “Charity Helpers”
Can someone help me please
I want to run a macro when cell b2 is = to “yes” in worksheet called “Charity Helpers”
Re: Macro To Run On Cell Answer
Hi,
Put this in the worksheet change event in your worksheet module for help hover over the word code.
Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.Range("B2").Value = "Yes" Then Call MyMacro
End Sub
What does your macro consist of perhaps it could be incoroporated into the worksheet change event.
Re: Macro To Run On Cell Answer
Reafidy thanks but i dont think i explained correctly
I want to run a macro when cell b2 is = to “yes” in worksheet called “Charity Helpers” which hides a column "AF" in sheet called "feb" this is "hidemacro"
and then reverses it if the answer is "no" or blank this is "unhidemacro"
i have recorded these and if i run them manualy on there own they work perfect
Sub unhidemacro()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Range("AF3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Columns("AF:AF").Select
Selection.EntireColumn.Hidden = True
Range("D3:AE3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Display More
Sub hidemacro()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Columns("AE:AG").Select
Range("AG1").Activate
Selection.EntireColumn.Hidden = False
Range("AF3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("D3:AF3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("D3:AE3").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Display More
Re: Macro To Run On Cell Answer
Hi there,
Put something like this in the worksheet code itself (not in a module). The code will run and call your macros depending on what the value is in B2
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case ActiveSheet.Range("B2").Value
Case Is = "Yes"
Call HideMacro
Case Is = "No"
Call UnhideMacro
Case Is = ""
Call UnhideMacro
End Select
End Sub
Display More
Regards,
Averil
Re: Run Macro On Certain Value Being Entered In Cell
Thanks to Averil and Dave
Averil i have put your code into my worksheet called "Carity Helpers" this is the work sheet where cell "b2" has to be yes or no, the work sheet which where i want to use the hide macros is called "events" what is happening is the hide macros are hiding the columns in "charity Helpers" not "events" it must be my code that is wrong could you please show me how to run my code on specific worksheet only, "hidemacro" And "unhidemacro"
Thanks
Regards John
Re: Run Macro On Certain Value Being Entered In Cell
Here is you unhide macro which you want to run on the events worksheet.
Note there is no select.
Sub unhidemacro()
Application.ScreenUpdating = False
With Worksheets("Events")
.Unprotect
.Columns("AF:AF").EntireColumn.Hidden = True
With .Range("D3:AF3")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
With .Range("AE3")
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.Borders(xlInsideVertical).LineStyle = xlNone
End With
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
End Sub
Display More
Re: Run Macro On Certain Value Being Entered In Cell
Averil thanks for showing me how to get the code to work only on "events" worksheet,
but the code you did for me to put into the "charity Helpers" worksheet will not work when i input yes or no in cell b2 is it because im using a drop down data validation
Re: Run Macro On Certain Value Being Entered In Cell
To all, check out the BorderAround Method to save a lot of code lines.
John, the closing code tag is [noparse][/code][/noparse] not [code/]
Re: Run Macro On Certain Value Being Entered In Cell
Thanks Dave - I never knew about the BorderAround method!
John,
I have tested this on my computer and seems to work fine. I have this code in a module:
Sub Hidemacro()
Application.ScreenUpdating = False
With Worksheets("Events")
.Unprotect
.Range("D3:AF3").BorderAround (xlContinuous)
.Columns("AF:AF").EntireColumn.Hidden = True
End With
End Sub
Sub UnHidemacro()
Application.ScreenUpdating = False
With Worksheets("Events")
.Unprotect
.Range("D3:AF3").BorderAround (xlContinuous)
.Columns("AF:AF").EntireColumn.Hidden = False
End With
End Sub
Display More
And the earlier code in the "Charity Workers" sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case ActiveSheet.Range("B2").Value
Case Is = "Yes"
Call Hidemacro
Case Is = "No"
Call UnHidemacro
Case Is = ""
Call UnHidemacro
End Select
End Sub
Display More
If you like, I can post the file I have been working on.
Cheers,
Averil
Re: Run Macro On Certain Value Being Entered In Cell
Averil,
You are not turning screenupdating back on.
John,
I didnt suggest the borderaround because it was difficult to see what you are actually trying to do, it looked to me like you where trying to put borders around AE3 as well as D3 and D3:AF3. I just cut the code you already had down as to not cause errors.
Re: Run Macro On Certain Value Being Entered In Cell
Kia ora!
Oops, didn't realise I'd forgotten to turn it back on... thanks for picking that up!
Cheers,
Averil
Re: Run Macro On Certain Value Being Entered In Cell
Thanks Averil and Dave
i have done everything you have both said but it still not working would it be something like the format of cell b2 in charity workers ???
Averil could you post the file please
Thanks Again to you both
Regards
John
Re: Run Macro On Certain Value Being Entered In Cell
See attached
Re: Run Macro On Value Being Entered
Thanks Dave
i will try it
Regards
John
Re: Run Macro On Value Being Entered
Hi John,
Sorry, I have been away on holiday. I hope Dave's file has helped you?
Regards,
Averil
Don’t have an account yet? Register yourself now and be a part of our community!