Macro and range names

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My question is this

Using the VBE I have rename sheetxx to shtInpinfo - Do I need the all of the
following code to use the range name "valid"?

If shtInpInfo.Range("valid").value <> 0 Then
shtInput.[button 17].Visible = False
Beep
End If
 
There are 2 different kinds of named ranges; global and sheet specific. If
the named range is sheet specific then you need to have the code as you have
written it. If the named range is global then you can get away with just

If Range("valid").value <> 0 Then

That being said I would stick with the code that you have as it should work
in both circumstances.

When in doubt being more specific with your references will always work out
for you.
 
Thanks for the third time.

Jim Thomlinson said:
There are 2 different kinds of named ranges; global and sheet specific. If
the named range is sheet specific then you need to have the code as you have
written it. If the named range is global then you can get away with just

If Range("valid").value <> 0 Then

That being said I would stick with the code that you have as it should work
in both circumstances.

When in doubt being more specific with your references will always work out
for you.
--
HTH...

Jim Thomlinson


Brad said:
My question is this

Using the VBE I have rename sheetxx to shtInpinfo - Do I need the all of the
following code to use the range name "valid"?

If shtInpInfo.Range("valid").value <> 0 Then
shtInput.[button 17].Visible = False
Beep
End If
Click to expand...
Click to expand...
 
If shtInpInfo is the active sheet you can use this:

If Range("valid").value <> 0 Then
shtInput.[button 17].Visible = False
Beep
End If

If it is not the active sheet, you need to state the sheet name just as your
did.
 
I personally avoid that kind of thing. The problem with it is "If shtInpInfo
is the active sheet ". Why leave it to chance. At some point you are going to
alter the flow of your program and potentially change which sheet is active
and whether a named range is global or local. Now this procedure may not work
any more although it is hard to tell that since it is not always obvious
which sheet is active at any given time and whether a named range is global
or local... you will never go wrong being more explicit than less...
--
HTH...

Jim Thomlinson


Alan said:
If shtInpInfo is the active sheet you can use this:

If Range("valid").value <> 0 Then
shtInput.[button 17].Visible = False
Beep
End If

If it is not the active sheet, you need to state the sheet name just as your
did.


Brad said:
My question is this

Using the VBE I have rename sheetxx to shtInpinfo - Do I need the all of
the
following code to use the range name "valid"?

If shtInpInfo.Range("valid").value <> 0 Then
shtInput.[button 17].Visible = False
Beep
End If
Click to expand...
Click to expand...
 
I guess I didn't read your question correctly, and took you for a beginner,
thus my simple answer. I apologize. If you were talking about the usage of
shtInpinfo in the line:

If shtInpInfo.Range("valid").Value <> 0 Then

should read

If Sheets("shtInpInfo").Range("valid").Value <> 0 Then


Regards,

Alan

Jim Thomlinson said:
I personally avoid that kind of thing. The problem with it is "If
shtInpInfo
is the active sheet ". Why leave it to chance. At some point you are going
to
alter the flow of your program and potentially change which sheet is
active
and whether a named range is global or local. Now this procedure may not
work
any more although it is hard to tell that since it is not always obvious
which sheet is active at any given time and whether a named range is
global
or local... you will never go wrong being more explicit than less...
--
HTH...

Jim Thomlinson


Alan said:
If shtInpInfo is the active sheet you can use this:

If Range("valid").value <> 0 Then
shtInput.[button 17].Visible = False
Beep
End If

If it is not the active sheet, you need to state the sheet name just as
your
did.


Brad said:
My question is this

Using the VBE I have rename sheetxx to shtInpinfo - Do I need the all
of
the
following code to use the range name "valid"?

If shtInpInfo.Range("valid").value <> 0 Then
shtInput.[button 17].Visible = False
Beep
End If
Click to expand...
Click to expand...
Click to expand...
 
shtInpInfo is the code name for the sheet and not the tab name so I stand by
my original code. I have been helping Brad to get away from using Sheets("Tab
name"). style code since it can generate errors if the end user modifies the
tab name...
--
HTH...

Jim Thomlinson


Alan said:
I guess I didn't read your question correctly, and took you for a beginner,
thus my simple answer. I apologize. If you were talking about the usage of
shtInpinfo in the line:

If shtInpInfo.Range("valid").Value <> 0 Then

should read

If Sheets("shtInpInfo").Range("valid").Value <> 0 Then


Regards,

Alan

Jim Thomlinson said:
I personally avoid that kind of thing. The problem with it is "If
shtInpInfo
is the active sheet ". Why leave it to chance. At some point you are going
to
alter the flow of your program and potentially change which sheet is
active
and whether a named range is global or local. Now this procedure may not
work
any more although it is hard to tell that since it is not always obvious
which sheet is active at any given time and whether a named range is
global
or local... you will never go wrong being more explicit than less...
--
HTH...

Jim Thomlinson


Alan said:
If shtInpInfo is the active sheet you can use this:

If Range("valid").value <> 0 Then
shtInput.[button 17].Visible = False
Beep
End If

If it is not the active sheet, you need to state the sheet name just as
your
did.


My question is this

Using the VBE I have rename sheetxx to shtInpinfo - Do I need the all
of
the
following code to use the range name "valid"?

If shtInpInfo.Range("valid").value <> 0 Then
shtInput.[button 17].Visible = False
Beep
End If
Click to expand...
Click to expand...
Click to expand...
 
Back
Top