Referencing Object on Hidden Worksheet 1

[AGlazer]

Hi, everyone.

Quick question. I have an embedded object on a hidden worksheet that I want to be able to access. Before I hid the sheet, this code worked:

Sheets("DataSheet&quot.Select
ActiveSheet.Shapes("Object 2&quot.Select
Selection.Verb Verb:=xlPrimary
Sheets("RFP Question Selection&quot.Select
Range("D2&quot.Select

I know, though reading this forum, that you can't do a 'select' on a hidden sheet. Is there another way to do this?

Thanks!

Aaron

 

[JPMontreal]

Hi,

Try to use the full name as

worksheets("DataSheet&quot.shapes("x&quot...


Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca

 

[Bowers74]

I've never used the Verb method, but (from the hip) I think this could work for you:

Sheets("DataSheet").Shapes("Object 2").Verb Verb:=xlPrimary
    Sheets("RFP Question Selection").Select
    Range("D2").Select

I hope this helps!




Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[AGlazer]

Hey guys,

Thanks to both of you.

Should have mentioned that I tried that method, too. Unfortunately, I get an 'object doesn't support this method' error when I do it that way.

Any other ideas?

Thanks again!

Aaron

 

[Bowers74]

This will unhide the sheet so you can select it, then it hides it. The Application.ScreenUpdating stops the screen from "jumping around" while the code does it's thing.

Application.Screenupdating = false
sheets("DataSheet").visible = true
sheets("DataSheet").Select
ActiveSheet.Shapes("Object 2").Select
    Selection.Verb Verb:=xlPrimary
sheets("DataSheet").visible = xlsheetveryhidden
    Sheets("RFP Question Selection").Select
    Range("D2").Select
Application.Screenupdating = true

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[AGlazer]

Mike,

Thanks. Will that work if the entire Workbook is protected, though? I want to make sure that people can't unhide it manually.

Aaron

 

[Bowers74]

The xlSheetVeryHidden hides your sheet so that the user can't unhide it using the Format->Sheet->Unhide... command.

Is your entire Workbook protected or is just the Worksheet Protected?

If your workbook is protected uning the "Structure" protection you need to unprotect it before unhiding and hiding sheets.

If the Worksheet is protected, you could theoretically remove the protection since it will be VeryHidden.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[AGlazer]

Mike,

It will be the entire workbook that is protected -- this is designed to go to a client, and I only want them to be able to modify certain things. I won't be able to 'unprotect' the entire workbook, unless I can do that through a macro and never have the workbook unprotected for the client to edit it.

Is there a way to do that?

Thanks again for all your help,

Aaron

 

[Bowers74]

This should do the trick:

Application.Screenupdating = false
ThisWorkbook.Unprotect Password:="YourPassword"
    Sheets("DataSheet").Visible = true
    sheets("DataSheet").Select
    ActiveSheet.Shapes("Object 2").Select
    Selection.Verb Verb:=xlPrimary
    sheets("DataSheet").visible = xlsheetveryhidden
    Sheets("RFP Question Selection").Select
    Range("D2").Select
ThisWorkbook.Protect Password:="YourPassword", Structure:=True, Windows:=False
Application.Screenupdating = true

I hope "this" helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[AGlazer]

Mike and all,

Thanks for your help. Turns out there is an easy way to do this -- I just wasn't referring to the object correctly. This works, even on a hidden & protected sheet:

Worksheets("DataSheet&quot.OLEObjects(2).Verb Verb:=xlOpen

Again, thanks for all your help.

Aaron

 

[SkipVought]

Nearly all Excel Objects can and ought to be referenced WITHOUT using SELECT or ACTIVATE.

In your original example...

    Sheets("DataSheet").Shapes("Object 2").Verb Verb:=xlPrimary

would do just fine.

Activating and Selecting, for the most part, slows down your processing.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[AGlazer]

Skip,

Actually, I tried that command, and the "Shapes" command rejects Verb, saying the object doesn't support the method, but it allows me to select the object and then run the Verb command. Makes no sense to me, but just thought I should let you know.

Thanks again,

Aaron

 

[SkipVought]

This syntax does not error out. I am not sure what Verb does

    ActiveSheet.Shapes(1).OLEFormat.Object.Verb Verb:=xlPrimary

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

This syntax does not error out. I am not sure what Verb does

    ActiveSheet.Shapes(1).OLEFormat.Object.Verb Verb:=xlPrimary

Verb is a member of the OLEFormat and OLEObject Class.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[AGlazer]

Cool -- that was what I was missing ("OLEFormat.Object&quot

Much appreciated.

Verb lets you open the embedded file in the original application.

Thanks again,

Aaron