xLSheetVeryHidden quandry

[davefish]

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.

 

[TonyJollans]

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

 

[xlbo]

Have a look at the WITH construct

WITH sheets("Sheetname&quot
.range("A1&quot.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

 

[combo]

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

 

[davefish]

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&quot.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:...............

 

[xlbo]

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

Range("A1&quot.select
Selection.value = "Boing"

you can normally get rid of the 2 selects like:
Range("A1&quot.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

 

[murad5]

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"

 

[davefish]

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