Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel vba

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

Excel vba

Resolved · Medium Priority · Version 2007

Reply Fri 16 Sep 2011, 16:19Delegate Carole said...

Carole has attended:
Excel VBA Intro Intermediate course

Excel vba

Hi,

I want to show/hide 4 rows & a command button (assigned to a print macro) dependant on a Yes/No response. i have the following code:

If Range("E63") = "Yes" Then

Rows("37:40").EntireRow.Hidden = False
ActiveSheet.Button_16.Visible = True

Else
Rows("37:40").EntireRow.Hidden = True
ActiveSheet.Button_16.Visible = False

End If

When I run it, I get "Runtime Error '438': Object doesn't support this property or method"

If I debug, it highlights the "ActiveSheet" row

Thanx

For upcoming training course dates see: Pricing & availability

Reply Mon 19 Sep 2011, 10:27Trainer Simon said...

RE: Excel vba

Hi Carole,

Thank you for your question.

The problem with code is accessing the command button using the Activesheet command.

What I suggest is you select the sheet you are working with as the first line of code(be explicit with the sheet you are working with).

Once the sheet has been explicitly selected then you can use the command cmdButton.visible = true.

Please find the code below and replace the sheet name and button name:

Sheets("Test Sheet").select

If Range("E63") = "Yes" Then

Rows("37:40").EntireRow.Hidden = False

cmdTest.Visible = True

Else

Rows("37:40").EntireRow.Hidden = True

cmdTest.Visible = False

End If

I hope this answers your question.

Regards

Simon

Reply Mon 19 Sep 2011, 10:27Trainer Simon said...

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Sun 25 Sep 2011: Automatically marked as resolved.

Excel articles

· Finding Your Way Around Excel 2007
· Tips To Help You Excel
· Microsoft Excel Training: Not Just for Newbies Anymore
· Using The Drawing Toolbar In Excel
· Excel and Inventory Management

Please browse our web site to find out more about
excel-courses and other Microsoft training courses.

Excel tip:

Create a hyperlink navigation sheet

In large files, it is often useful to have a front sheet with hyperlinks to the key databases and summary calculations in your spreadsheet. Hyperlinks can save you and (more importantly) those less familiar with your spreadsheet a great deal of pointless scrolling between and within sheets.

Hyperlinks appear as underlined text and can jump to any cell or range name in your file. You can also use hyperlinks to jump to other files.

To create a hyperlink to a location in the active workbook: (1) Select the cell that contains the text you want to use as the hyperlink and choose Insert|Hyperlink.(2)Click Place in this document.(3)Choose the sheet you want to link to or the range name from the list of "Defined Names".(4)If necessary, type the cell reference in the Type in the cell reference box. (5) Click OK.

View all Excel hints and tips