Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Is it possible to detect Excel version?

  Asked By: George    Date: Oct 18    Category: MS Office    Views: 934
  

In my post "How can I hide a single sheet", I guess if I had to ask
the question is that I hade tried to record a macro hiding the sheet,
and I could not: in my Windows Menu, (my Excel version is very old!
1997), I have the option to hide a Workbook, but not a single sheet.
So even if I did not have time to try the many answers that I
received, I am afraid that it won't work in my version, but the users
will probably have more recent versions then I do.

So from one question to another one:
Is it possible with VBA, (like I used to do all the time when I wrote
Java script) to detect which version the user is working on, and if
yes, starting with what version was this his option to hide a single
sheet was added?

Share: 

 

9 Answers Found

 
Answer #1    Answered By: Vidos Fischer     Answered On: Oct 18

You have Excel 97? I'm pretty sure that version  had sheet  hiding on the
menu, although I don't currently have a version I can run, to check.

Format / Sheet / (Un)Hide is where it would be. For some reason, it's not
available in the right-click options on the tabs at the bottom.

Excel 97 was a big step forward from Excel 95, and the 2000, XP and 2003
versions were just minor improvements. (I haven't tried 2007.)

AFAIK there is no explicit way of checking the Excel version from VBA. (At
one stage, I think I used a cheat to work out if I was on 97, rather than
2000, but I don't remember the details, and don't seem to have the code any
more. I think I finally dropped the version-specific code and simply wrote
my own versions of the functions involved.)

 
Answer #2    Answered By: Hoor Khan     Answered On: Oct 18

I have a computer with Excel 97 handy and just checked it. Yes,
the "Format/Sheet/Hide" command is available.

 
Answer #3    Answered By: Hugo Williams     Answered On: Oct 18

Thanks to all of you who have helped me so much!

As I thought, once the "All Queries" sheet  is hidden, no macro  can
reach it.

So at the beginning of each macro that needed a webquery, I wrote:

Application.ScreenUpdating=False (thanks for this, Pascal)
ThisWorkbook.Sheets("All Queries").Visible = True

and at the end:
ThisWorkbook.Sheets("All Queries").Visible = False
Application.ScreenUpdating=True

It works like magic!
I never have thought I could do that.
The people who will use it will wonder!


For now, since I still need to see that sheet very often, I did not
use Dave's proposal for :
ThisWorkbook.Sheets("Sheet2").Visible = xlSheetVeryHidden
I guess I'll introduce that only later.

The same with Private: Once it is private, you can't go to the sub
from the workbook, so I'll keep that for later too!

The only thing that I have tried (copied from the VB Help from Excel,
that I find so unhelpful), was to hide  the sheet when the book opens,
using:

Private Sub App_WorkbookOpen(ByVal "my bookname.xls" As
Workbook)
Worksheets("All Queries").Visible = False
End Sub

and I got the error message:
Compile Error: Expected identifier


Then I tried simply
Private Sub App_WorkbookOpen()
Worksheets("All Queries").Visible = False
End Sub

I did not get any compile error... but it did not work.

Sure I just have to hide it before I save, and it works perfect.

Still, eventually I'll come back and ask how to use the
App_WorkbookOpen.

Because the more I go the more ideas I have!

OK, but I am not so far yet.

 
Answer #4    Answered By: Amelia Schmidt     Answered On: Oct 18

I have this code within my add-in, because of version  differences:

If Val(Application.Version) < 10 Then
Application.CalculateFull
Else
Application.CalculateFullRebuild
End If

You could probably use something similar. As far as I know:

< 8 then Excel 95 or earlier
= 8 then Excel 97
= 9 then Excel 2000
= 10 then Excel 2002 or XP
= 11 then Excel 2003
= 12 then Excel 2007

 
Answer #5    Answered By: Kristen Chavez     Answered On: Oct 18

I use vers 97
The VBA (macro) command to hide  a sheet:

Sheets("Xxxxxx").Visible = True

Where "Xxxxxx" is the title of the sheet.


> Is it possible with VBA...to detect  which version  the user is
working on Thanks in advance,Louise

YEP.
Excel 97 also has version. I ran some experiments to see what I got
with various calls:
Debug.Print " Creator = "; Application.Creator
Debug.Print " OS = "; Application.OperatingSystem
Debug.Print " VERS = "; Application.Version

For some help, Go into menu  > Tools > Oprions and turn on "Auto List
Members" Then type CTRL+Space to see all possible options with the
cursor in a module. And if you type "Application." it will now show
you a popup menu the plssible options for "Application.".

 
Answer #6    Answered By: Jennie Harris     Answered On: Oct 18

I notice that you've set the sheet  to "very hidden". This might be further
than you want to go. "Very hidden" can only be unhidden by VBA code and is
there to protect things that the user MUST NOT see - usually in conjunction
with a password-protected VBA project.

"Ordinary" hidden would be plenty for things that you'd prefer that the user
doesn't see.

You also say that macros can't reach your sheet once it is hidden. In fact,
macros (VBA code) can access hidden sheets without trouble. It's only when
they try to do things that would normally cause the sheet or its cells to
come into view that you'll have trouble. I.e. you can't select or activate
cells on a hidden sheet, but the code can make normal direct use of the
cells without problems.

 
Answer #7    Answered By: Melissa King     Answered On: Oct 18

I guess this would need to be done right when the workbook opens, and
this was one of the things that I have tried, as described in my last
post in his topic, and that did not work.

Now in your reply, you wrote:
> "I notice that you've set the sheet  to "very hidden"."
I think you misread my post.
I wrote:
> For now, since I still need to see that sheet very often, I
did not use Dave's proposal for ThisWorkbook.Sheets("Sheet2").Visible
= xlSheetVeryHidden
>I guess I'll introduce that only later.

You also wrote:
>In fact, macros (VBA code) can access hidden sheets without trouble.
It's only when they try to do things that would normally cause the
sheet or its cells to come into view that you'll have trouble. I.e.
you can't select or activate cells on a hidden sheet,

But this is exactly what my macros do when they use the hidden sheet.
When a macro  need to update a webquery, it has to select the right
one, so I had to name the cell where the red "!" shows up indicating
that this is the beginning of the webquery that I want to update.

That is why I was so happy to be able to un hide  them but to
deactivate the screen updating,
Application.ScreenUpdating=False
because else the hiding  of the sheet would have been pretty useless:
without that part of code, I came back exactly to the situation that
I wanted to avoid.

All your replies brought me to try other things that did not work
well, but I will post  them separately, because for the
moment, without them everything seems to work well, and I still have
more urgent things in order to complete a first test workbook to send
to the site administrator.

One of those tasks being to replace by functions some cells where I
have so long formulas with tons of IF(... and AND( ; ;) that the
formula takes almost 3 lines, and I myself, when I look at them, I
can't remember why I wrote that, except that I know that they had to
be like that.
Now that I know how to write functions, I could easier document the
formulas with comments.

 
Answer #8    Answered By: Clayton Richardson     Answered On: Oct 18

Ah OK. I hadn't suggested "very hidden", which is what threw me.

 
Answer #9    Answered By: Adelinda Fischer     Answered On: Oct 18

I knew I would get mixed up! Got so much help on that topic!
Thanks anyway, and again to all of you

 
Didn't find what you were looking for? Find more on Is it possible to detect Excel version? Or get search suggestion and latest updates.




Tagged: