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 biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

xLSheetVeryHidden quandry

Status
Not open for further replies.

davefish

Technical User
Jul 26, 2002
169
GB
If anyone can offer an explanation it would be great. I have a number of Sheets in a workbook, five in all, some of which I wish to hide using the xlSheetVeryHidden property. However, when I run my code I get 'Method of select object failed message' I think due to code referencing the sheet. I have the 'EnableSelection' switched to xlNoRestictions could there be any other reason for the message. If I run the code with the sheets visible, no problem.


Hellp!

DaveFish

PS: The cell references place data on the sheet from vba.
 
Hi davefish,

You can't Select a hidden sheet. Why do you want to? You can do (almost) anything without Selecting. If you post your code someone will help you out with it.

Enjoy,
Tony
 
Have a look at the WITH construct

WITH sheets("Sheetname")
.range("A1").value = "this is how to reference a hidden sheet"
end WITH

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
 
This is due to the way of referencing by Select method - you can't select hidden sheet or any part of it. Instead of:

[tt]Something.Select
Selection.DoSomeThing[/tt]

use:

[tt]Something.DoSomeThing[/tt]

Will also be faster.

combo
 
Hmm! It looks like I may have to re-think my structure. I haven't tried to withSheets approach but I'm a bit confused on the DoSomeThing. All I can find in the browser is DoEvent which I don't hink does it for me! Here's a bit of my code:-

I have nested Case Select to identify the parameters I require. Then I place a value in a Cell corresponding to the item in a list. Thinking about it, maybe an multi-dimentional array will cure my ill's? Does this explanation make sense? if not lety me know what additional info you need.


Best Regards

Davefish

Function BOM_Create()

ActiveWorkbook.Sheets("BOM_MASTER").Activate

Select Case MySettings.Charger_I

Case 1 To 50:
Select Case MySettings.Bp_Cmb_BattBus_Volt
Case 220:
Sheet30.Select
[C73].Select
ActiveCell.Value = 88: GoTo Finish:
Case 110:
Sheet30.Select
[C74].Select
ActiveCell.Value = 99: GoTo Finish:...............

 
dosomething is pseudocode
It was just there to illustrate the point that if you have 2 lines that say, for example

Range("A1").select
Selection.value = "Boing"

you can normally get rid of the 2 selects like:
Range("A1").value = "Boing"

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
 
To translate xlbo's example to match your code, you would use

Case 1 To 50:
Select Case MySettings.Bp_Cmb_BattBus_Volt
Case 220:
Sheet30.[C73].Value = 88: GoTo Finish:
Case 110:
Sheet30.[C74].Value = 99: GoTo Finish:...............

where sheet30.[C74] is equivalent to "something" and .value = 88 is equivalent to "DoSomething"
 
Thanks to all of you for your suggestions. I revamped my code to take advantage of both the 'With' Statement and the code shortning. Both worked a treat. Keep up the good work guy's

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top