Excel macros and user-defined function

[jacky89]

I recorded a command and this is the code:

Sub Macro1()
'
' Macro1 Macro
' hide row
'

'
Rows("54:54").Select
Selection.EntireRow.Hidden = True
End Sub


How do I make this a function that I can use in a cell? For example I want this set of code to be named as function HIDEROW54. I want to be able to write a function that can be used in an Excel cell. E.g. if(b3="1",HIDEROW54,"")

Can any body give me some advice on how to do a user-defined excel function that can do this?

Thanks!

 

[GlennUK]

You cannot use a function to alter Excel attributes. Functions return results, they cannot perform changes .... that is the difference between Subs and Functions. Use a Worksheet event Sub to do what you want.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Fenrirshowl]

I'd suggest need a worksheet event rather than a macro driven by a function.

If you right-click the sheet tab at the bottom and select view code, you get to a VB module for that specific sheet.

If you change the "General" drop down list to "Worksheet" and change the event to "Calculate", say, you could add something like the following:

Private Sub Worksheet_Calculate()
If Range("B3") = 1 Then Range("A54").EntireRow.Hidden = True
End Sub

Every time the sheet is calculated it will check the value of B3 and hide row 54 if it has the value 3. You could expand the event to unhide row 54 if the value in B3 does not equal 3.

HTH

 

[jacky89]

Cool, THanks, I will try it