Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel macros and user-defined function

Status
Not open for further replies.

jacky89

Technical User
May 28, 2007
2
US
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!
 
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.
 
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:

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top