LinkBack URL
About LinkBacksIs there a way to disable a user from changing the worksheet name?
Not as in "Save As" but the worksheet.
For example - right hand click on worksheet -> rename is able to be
done by everyone.
Is there a way to disable this?
Is there a way to disable a user from changing the worksheet name?
Not as in "Save As" but the worksheet.
For example - right hand click on worksheet -> rename is able to be
done by everyone.
Is there a way to disable this?
One really does need VBA to do so.
Just Click from the menubar on Tools -> Securtiy -> Secure Workbook
As the box pops up, make sure the checkbox 'Structure' is True.
"RigasMinho" wrote:
> Is there a way to disable a user from changing the worksheet name?
>
> Not as in "Save As" but the worksheet.
>
> For example - right hand click on worksheet -> rename is able to be
> done by everyone.
>
> Is there a way to disable this?
>
>
You can protect the book which will lock the users out from renaming sheets,
but otherwise nope. That is why you are always better to code your macros to
the sheets CodeName as opposed to the sheets tab name. The end user can not
(without getting inot the code) change the code name. If you want help with
using the code name (you may have to re-write a fair bit of your code) then
just let me know...
--
HTH...
Jim Thomlinson
"RigasMinho" wrote:
> Is there a way to disable a user from changing the worksheet name?
>
> Not as in "Save As" but the worksheet.
>
> For example - right hand click on worksheet -> rename is able to be
> done by everyone.
>
> Is there a way to disable this?
>
>
Here is my code - wondering what you mean by code name.
are you talking about this:
Say Master Questions was the sheet name but excel really puts it under
Sheet2
I would put in sheet2 instead of master questions?
Dim rngLookup As String ' Value to search for
Dim rngFound As Range ' Cell rngLookup is found in
Dim firstAddress As String 'Cell address of the first value found
Dim wksDisplayResults As Worksheet ' Output sheet
Dim wksMaster As Worksheet 'Master Questions sheet
Dim ri As Long ' Row Index used to know which row results should
paste into
Dim bContinue As Boolean ' Used to stop find loop
Dim emptycell As Range 'used to find next empty cell
If Acquisition_Checkbox.Value = False Then
Set wksMaster = Worksheets("Master Questions")
Set wksDisplayResults = Worksheets("Removed Questions")
ri = wksDisplayResults.Cells(Rows.Count, 1).End(xlUp).Offset(1,
0).Row
rngLookup = "ac"
'Before beginning loop, copy the header to result sheet
wksMaster.Range("a1").EntireRow.Copy wksDisplayResults.Range("a1")
' Find Lookup Value
With Worksheets("Master Questions").Range("e2:e65000")
Set rngFound = .Find(rngLookup, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=True)
'Return message if value not found
If rngFound Is Nothing Then
MsgBox ("The search item " & rngLookup & " was not
found")
Else
firstAddress = rngFound.Address
bContinue = True
'Continue looping until bcontinue is false
Do While bContinue
'Cut row into result sheet, then increment the row
index
rngFound.EntireRow.Cut wksDisplayResults.Rows(ri)
ri = ri + 1
'Find the next cell containing lookup value
Set rngFound = .FindNext(rngFound)
'If range found is not nothing, then bContinue will
remain true
bContinue = Not rngFound Is Nothing
'Then check to see if rngfound's address is equal
to firstaddress
If bContinue = True Then bContinue =
rngFound.Address <> firstAddress
Loop
End If
End With
End If
Jim Thomlinson wrote:
> You can protect the book which will lock the users out from renaming sheets,
> but otherwise nope. That is why you are always better to code your macros to
> the sheets CodeName as opposed to the sheets tab name. The end user can not
> (without getting inot the code) change the code name. If you want help with
> using the code name (you may have to re-write a fair bit of your code) then
> just let me know...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "RigasMinho" wrote:
>
> > Is there a way to disable a user from changing the worksheet name?
> >
> > Not as in "Save As" but the worksheet.
> >
> > For example - right hand click on worksheet -> rename is able to be
> > done by everyone.
> >
> > Is there a way to disable this?
> >
> >
Never mind I got it:
sheet1.name
thanks
RigasMinho wrote:
> Here is my code - wondering what you mean by code name.
> are you talking about this:
>
> Say Master Questions was the sheet name but excel really puts it under
> Sheet2
>
> I would put in sheet2 instead of master questions?
>
> Dim rngLookup As String ' Value to search for
> Dim rngFound As Range ' Cell rngLookup is found in
> Dim firstAddress As String 'Cell address of the first value found
> Dim wksDisplayResults As Worksheet ' Output sheet
> Dim wksMaster As Worksheet 'Master Questions sheet
>
> Dim ri As Long ' Row Index used to know which row results should
> paste into
> Dim bContinue As Boolean ' Used to stop find loop
> Dim emptycell As Range 'used to find next empty cell
>
> If Acquisition_Checkbox.Value = False Then
> Set wksMaster = Worksheets("Master Questions")
> Set wksDisplayResults = Worksheets("Removed Questions")
>
> ri = wksDisplayResults.Cells(Rows.Count, 1).End(xlUp).Offset(1,
> 0).Row
>
> rngLookup = "ac"
>
> 'Before beginning loop, copy the header to result sheet
> wksMaster.Range("a1").EntireRow.Copy wksDisplayResults.Range("a1")
>
> ' Find Lookup Value
> With Worksheets("Master Questions").Range("e2:e65000")
> Set rngFound = .Find(rngLookup, _
> LookIn:=xlValues, _
> LookAt:=xlPart, _
> MatchCase:=True)
> 'Return message if value not found
> If rngFound Is Nothing Then
> MsgBox ("The search item " & rngLookup & " was not
> found")
> Else
> firstAddress = rngFound.Address
> bContinue = True
>
> 'Continue looping until bcontinue is false
> Do While bContinue
> 'Cut row into result sheet, then increment the row
> index
>
>
>
> rngFound.EntireRow.Cut wksDisplayResults.Rows(ri)
> ri = ri + 1
>
>
>
> 'Find the next cell containing lookup value
> Set rngFound = .FindNext(rngFound)
> 'If range found is not nothing, then bContinue will
> remain true
> bContinue = Not rngFound Is Nothing
> 'Then check to see if rngfound's address is equal
> to firstaddress
> If bContinue = True Then bContinue =
> rngFound.Address <> firstAddress
>
>
> Loop
> End If
> End With
>
>
> End If
>
> Jim Thomlinson wrote:
> > You can protect the book which will lock the users out from renaming sheets,
> > but otherwise nope. That is why you are always better to code your macros to
> > the sheets CodeName as opposed to the sheets tab name. The end user can not
> > (without getting inot the code) change the code name. If you want help with
> > using the code name (you may have to re-write a fair bit of your code) then
> > just let me know...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "RigasMinho" wrote:
> >
> > > Is there a way to disable a user from changing the worksheet name?
> > >
> > > Not as in "Save As" but the worksheet.
> > >
> > > For example - right hand click on worksheet -> rename is able to be
> > > done by everyone.
> > >
> > > Is there a way to disable this?
> > >
> > >
That is exactly what I meant. You should however change the (Name) Sheet1 to
something more descriptive like shtMasterQuestions. This can be done through
properties.
--
HTH...
Jim Thomlinson
"RigasMinho" wrote:
> Never mind I got it:
>
> sheet1.name
> thanks
> RigasMinho wrote:
> > Here is my code - wondering what you mean by code name.
> > are you talking about this:
> >
> > Say Master Questions was the sheet name but excel really puts it under
> > Sheet2
> >
> > I would put in sheet2 instead of master questions?
> >
> > Dim rngLookup As String ' Value to search for
> > Dim rngFound As Range ' Cell rngLookup is found in
> > Dim firstAddress As String 'Cell address of the first value found
> > Dim wksDisplayResults As Worksheet ' Output sheet
> > Dim wksMaster As Worksheet 'Master Questions sheet
> >
> > Dim ri As Long ' Row Index used to know which row results should
> > paste into
> > Dim bContinue As Boolean ' Used to stop find loop
> > Dim emptycell As Range 'used to find next empty cell
> >
> > If Acquisition_Checkbox.Value = False Then
> > Set wksMaster = Worksheets("Master Questions")
> > Set wksDisplayResults = Worksheets("Removed Questions")
> >
> > ri = wksDisplayResults.Cells(Rows.Count, 1).End(xlUp).Offset(1,
> > 0).Row
> >
> > rngLookup = "ac"
> >
> > 'Before beginning loop, copy the header to result sheet
> > wksMaster.Range("a1").EntireRow.Copy wksDisplayResults.Range("a1")
> >
> > ' Find Lookup Value
> > With Worksheets("Master Questions").Range("e2:e65000")
> > Set rngFound = .Find(rngLookup, _
> > LookIn:=xlValues, _
> > LookAt:=xlPart, _
> > MatchCase:=True)
> > 'Return message if value not found
> > If rngFound Is Nothing Then
> > MsgBox ("The search item " & rngLookup & " was not
> > found")
> > Else
> > firstAddress = rngFound.Address
> > bContinue = True
> >
> > 'Continue looping until bcontinue is false
> > Do While bContinue
> > 'Cut row into result sheet, then increment the row
> > index
> >
> >
> >
> > rngFound.EntireRow.Cut wksDisplayResults.Rows(ri)
> > ri = ri + 1
> >
> >
> >
> > 'Find the next cell containing lookup value
> > Set rngFound = .FindNext(rngFound)
> > 'If range found is not nothing, then bContinue will
> > remain true
> > bContinue = Not rngFound Is Nothing
> > 'Then check to see if rngfound's address is equal
> > to firstaddress
> > If bContinue = True Then bContinue =
> > rngFound.Address <> firstAddress
> >
> >
> > Loop
> > End If
> > End With
> >
> >
> > End If
> >
> > Jim Thomlinson wrote:
> > > You can protect the book which will lock the users out from renaming sheets,
> > > but otherwise nope. That is why you are always better to code your macros to
> > > the sheets CodeName as opposed to the sheets tab name. The end user can not
> > > (without getting inot the code) change the code name. If you want help with
> > > using the code name (you may have to re-write a fair bit of your code) then
> > > just let me know...
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "RigasMinho" wrote:
> > >
> > > > Is there a way to disable a user from changing the worksheet name?
> > > >
> > > > Not as in "Save As" but the worksheet.
> > > >
> > > > For example - right hand click on worksheet -> rename is able to be
> > > > done by everyone.
> > > >
> > > > Is there a way to disable this?
> > > >
> > > >
>
>
How would you change the (name) of the sheet?
I couldnt find anything under properties.
Jim Thomlinson wrote:
> That is exactly what I meant. You should however change the (Name) Sheet1 to
> something more descriptive like shtMasterQuestions. This can be done through
> properties.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "RigasMinho" wrote:
>
> > Never mind I got it:
> >
> > sheet1.name
> > thanks
> > RigasMinho wrote:
> > > Here is my code - wondering what you mean by code name.
> > > are you talking about this:
> > >
> > > Say Master Questions was the sheet name but excel really puts it under
> > > Sheet2
> > >
> > > I would put in sheet2 instead of master questions?
> > >
> > > Dim rngLookup As String ' Value to search for
> > > Dim rngFound As Range ' Cell rngLookup is found in
> > > Dim firstAddress As String 'Cell address of the first value found
> > > Dim wksDisplayResults As Worksheet ' Output sheet
> > > Dim wksMaster As Worksheet 'Master Questions sheet
> > >
> > > Dim ri As Long ' Row Index used to know which row results should
> > > paste into
> > > Dim bContinue As Boolean ' Used to stop find loop
> > > Dim emptycell As Range 'used to find next empty cell
> > >
> > > If Acquisition_Checkbox.Value = False Then
> > > Set wksMaster = Worksheets("Master Questions")
> > > Set wksDisplayResults = Worksheets("Removed Questions")
> > >
> > > ri = wksDisplayResults.Cells(Rows.Count, 1).End(xlUp).Offset(1,
> > > 0).Row
> > >
> > > rngLookup = "ac"
> > >
> > > 'Before beginning loop, copy the header to result sheet
> > > wksMaster.Range("a1").EntireRow.Copy wksDisplayResults.Range("a1")
> > >
> > > ' Find Lookup Value
> > > With Worksheets("Master Questions").Range("e2:e65000")
> > > Set rngFound = .Find(rngLookup, _
> > > LookIn:=xlValues, _
> > > LookAt:=xlPart, _
> > > MatchCase:=True)
> > > 'Return message if value not found
> > > If rngFound Is Nothing Then
> > > MsgBox ("The search item " & rngLookup & " was not
> > > found")
> > > Else
> > > firstAddress = rngFound.Address
> > > bContinue = True
> > >
> > > 'Continue looping until bcontinue is false
> > > Do While bContinue
> > > 'Cut row into result sheet, then increment the row
> > > index
> > >
> > >
> > >
> > > rngFound.EntireRow.Cut wksDisplayResults.Rows(ri)
> > > ri = ri + 1
> > >
> > >
> > >
> > > 'Find the next cell containing lookup value
> > > Set rngFound = .FindNext(rngFound)
> > > 'If range found is not nothing, then bContinue will
> > > remain true
> > > bContinue = Not rngFound Is Nothing
> > > 'Then check to see if rngfound's address is equal
> > > to firstaddress
> > > If bContinue = True Then bContinue =
> > > rngFound.Address <> firstAddress
> > >
> > >
> > > Loop
> > > End If
> > > End With
> > >
> > >
> > > End If
> > >
> > > Jim Thomlinson wrote:
> > > > You can protect the book which will lock the users out from renaming sheets,
> > > > but otherwise nope. That is why you are always better to code your macros to
> > > > the sheets CodeName as opposed to the sheets tab name. The end user can not
> > > > (without getting inot the code) change the code name. If you want help with
> > > > using the code name (you may have to re-write a fair bit of your code) then
> > > > just let me know...
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "RigasMinho" wrote:
> > > >
> > > > > Is there a way to disable a user from changing the worksheet name?
> > > > >
> > > > > Not as in "Save As" but the worksheet.
> > > > >
> > > > > For example - right hand click on worksheet -> rename is able to be
> > > > > done by everyone.
> > > > >
> > > > > Is there a way to disable this?
> > > > >
> > > > >
> >
> >
It is your mean, manOriginally Posted by RigasMinho
Application.CommandBars("Ply").Enabled = False /true
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks