Sleeper: The difference between Hidden and VeryHidden

[Regouin]

Can someone explain the difference between hidden and veryhidden, i can see the difference with sheets, since hidden just makes them hidden and every user can easily get them back via the menu, but is this also applicable on the application, and if yes what is the difference. And when the application is hidden it just pops up when i try to open another instance of excel, which is not meant to happen.

[austenr]

Hidden Sheet could be shown in excel in Format | Sheets | Unhide. A very Hidden Sheet couldnt... only via code. Which makes this property very useful when you have sensitive data, and you don't want the user to see it...

[Aaron Blood]

Hidden Sheet could be shown in excel in Format | Sheets | Unhide. A very Hidden Sheet couldnt... only via code. Which makes this property very useful when you have sensitive data, and you don't want the user to see it...

If the data is sensitive, and you don't want users to see it...

You better not put it on a sheet! (veryhidden or not)

[brettdj]

Aarons point is of course correct

But as most people don't understand very hidden I will use it when I would prefer something to be as hidden while simultaneously not being too worried if it was ever discovered - say some surplus sheets which I want to keep linked to a model, or perhaps a sheet listing the changes I've made to the menu toolbars. I work in an office of literate Excel people yet none of them knew about very hidden.

[Regouin]

but you have to know that in a certain workbook there are veryhiddensheets, it is not that Excel lists them in any way, to tell you that the workbook uses veryhiddensheets, now you can easily set up a macro to check for you in every workbook but still you need to have a thorough understanding of Excel and VBA before that comes to mind.

Frank


[edit]
O, and it is not very sensitive data, I just dont want the user to mock around with it, but thats why i hide excel in the first place. Do you know if I can still send commands to veryhidden sheets or do they have to be visible for Excel to be able to do anything with them? Because if I can keep them hidden I'll just do that and then the user can only see a page which tells him to turn on macro's (kb anyone ).
[/edit]

[Jacob Hilderbrand]

You can work with very hidden sheets for the most part. If you wanted to print them you would need to make them visible first. If you copied them, they would still be hidden.

If you need to make them visible, just use Application.ScreenUpdating = False and the user will never know the difference.

[Regouin]

So if I get this straight, the only thing where i need the sheet to be visible is when i want to copy ranges to show them to the user, but as far as I can see it doesnt affect the normal course of action, and i can get VBA to get data from the sheets, write data to the sheet and all this while the sheets are veryhidden. Thing is that I have made a complete Userform Interface and I dont want my users to be able to interrupt the script and thus be able to edit the normal worksheets.

Is there also away to catch up on the ctrl-break? as in an on error command or is this hard or impossible to accomplish? I know it is a basic build in routine to be able to kick VBA out of infite loops and such, and still when they actually manage to break out of the code, manage to get Excel visible they would only find 1 sheet, since the rest of them is VeryHidden. And then they cant get into the code because it is pw protected. So I think you have to go through some distance to be able to get to the point where you can actually affect the data.

[Bob Phillips]

So if I get this straight, the only thing where i need the sheet to be visible is when i want to copy ranges to show them to the user

You don't even need to do it then, you can copy from a hidden worksheet. Just print as far as I can see.

Is there also away to catch up on the ctrl-break? as in an on error command or is this hard or impossible to accomplish?

No it is very easy, there is an Application.EnableCancelKey property, which has three states
- xlDisabled
- xlInterrupt
- and xlErrorHandler - allows the error to be trapped.

For example:

Public Sub TrapCtrlBreak() 
On Error GoTo errHandler 
Application.EnableCancelKey = xlErrorHandler 
Do 
DoEvents 
Loop 
Exit Sub 
errHandler: 
If Err.Number = 18 Then MsgBox "Break pressed" 
End Sub

You could also try setting Onkey to some custom procedure

Application.OnKey "^{BREAK}","myMacro"

but I have never used this myself.

[Regouin]

now with the visible thing, i select a range on 1 of my worksheets, copy it to a word document and then mail it using outlook, now when the particular sheet in Excel is hidden it sends an empty document (apart from the line I programmatically add in Word) and when i show it before selection and hide it straight afterwards it, it does what it is supposed to do.
And Xld, would this sub you've written automatically trigger once ctrl-break is pressed?

[Bob Phillips]

now with the visible thing, i select a range on 1 of my worksheets, copy it to a word document and then mail it using outlook, now when the particular sheet in Excel is hidden it sends an empty document (apart from the line I programmatically add in Word) and when i show it before selection and hide it straight afterwards it, it does what it is supposed to do.

I was thinking in an Excel context, not automation. Will have to test that when I have some time.

And Xld, would this sub you've written automatically trigger once ctrl-break is pressed?

Frank, as with all things, try it.

Here is a simple test harness. I have added an End to stop the code when you Ctrl-Break.

Public Sub TrapCtrlBreak()
On Error GoTo errHandler
Application.EnableCancelKey = xlErrorHandler
Do
DoEvents
Loop
Exit Sub
errHandler:
If Err.Number = 18 Then MsgBox "Break pressed"
End
End Sub
 
Sub TestCtrlBreak()
Dim i As Long
Dim x
TrapCtrlBreak
For i = 1 To 1000000000
x = "alphabet"
Next i
End Sub

[Regouin]

thanks xld you're the best

Ok i am having some trouble on where to insert this, cant find the appropriate place, so that it is always active and just reacts on ctrl-break

[Regouin]

Ok, I am playing with it but cant seem to get much further, do I need to integrate this in every sub to able to work? and i integrated it in a sub, and it does show the msgbox once stating that break is pressed, but then when you press again it does still goes into the normal ctrl break routine. I am kind of stuck here, isnt there a simple way of just rerouting the ctrl break till I tell it to stop doing that?. as you can reroute all kind of keys on the keyboard I think.

TIA
Frank

[Bob Phillips]

Ok, I am playing with it but cant seem to get much further, do I need to integrate this in every sub to able to work? and i integrated it in a sub, and it does show the msgbox once stating that break is pressed, but then when you press again it does still goes into the normal ctrl break routine. I am kind of stuck here, isnt there a simple way of just rerouting the ctrl break till I tell it to stop doing that?. as you can reroute all kind of keys on the keyboard I think.

Frank,

I just gave you an example. You will need to incorporate it into the code. What you would probably do is call the set routine in you application startoff, or maybe workb.ook open.

How you handle errors will depend upon your design, but for instance, you could just Resume in the errhandler clause, maybe after logging the event and educate the offending users .

[Regouin]

Ok, I can work with that, so i basically make a subroutine and call that from every other routine? i tried fiddling with the Workbook_open, but cant seem to get it right. And the only option users have for pressing ctrl+break is during input or msgbox (or they have to be real quick and be fast enough to interrupt it while it is executing code). I have tried playing with

application.onkey "^{BREAK}","warnsub"

and then reset it in the end

application.onkey "^{BREAK}"

but i cant get my finger behind this progress, it just doesnt make a difference, my warnsub just contains a msgbox telling them to stop trying to break it.

TIA
frank