Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Worksheet Name Change
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Worksheet Name Change
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Worksheet Name Change
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Worksheet Name Change
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Worksheet Name Change
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Worksheet Name Change
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Worksheet Name Change
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable Worksheet Name Change
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, man A p p l i c a t i o n . C o m m a n d B a r s ( \ " P l y \ " ) . E a b l e d = F a l s e / t r u -- vumia ----------------------------------------------------------------------- vumian's Profile: http://www.excelforum.com/member.php...fo&userid=3649 View this thread: http://www.excelforum.com/showthread.php?threadid=56717 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
disable brian baulsom's worksheet change based on cell value. | Excel Worksheet Functions | |||
Disable a macrobutton for a particular cell change? | Excel Programming | |||
how to disable listbox change event | Excel Programming | |||
Enable/Disable Worksheet Change Event code | Excel Programming | |||
Disable Worksheet change event | Excel Programming |