Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Hide AutoShapes On Excel Worksheet
Hello,
I have a workbook that is stored in 2 seperate places on the network. One of the copies satys as is and the other copy is placed in a different directory which is meant to be a view only copy. There are multiple worksheets within this workbook along with 2 AutoShapes that I do not want to be visible in the view only copy. I know how to hide the worksheets that I do not want to visible using VBA but I still have 2 issues that I need help with: 1. How can I hide the 2 AutoShapes in the view only copy using VBA. Both AutoShapes are placed on the same worksheet. 2. I would like to create a button (ActiveX Control) that I would use to hide the worksheets and the AutoShapes that I do not want to be visible in one click. Can I assign permissions to that button so that only I can make the contol perform its function? Any help, suggestions, or useful links will be greatly appreciated. Thank you. Dave Y |
#2
|
|||
|
|||
This may get you started:
Option Explicit Private Sub CommandButton1_Click() Dim wksNames As Variant Dim iCtr As Long Const PWD As String = "123" Dim testPWD As String testPWD = InputBox(Prompt:="What's the password, Kenny?") If testPWD < PWD Then Exit Sub End If wksNames = Array("sheet2", "Sheet3", "sheet5") For iCtr = LBound(wksNames) To UBound(wksNames) Worksheets(wksNames(iCtr)).Visible = xlSheetHidden Next iCtr Me.Shapes("autoshape 1").Visible = False Me.Shapes("autoshape 2").Visible = False End Sub I assumed that the autoshapes were on the sheet with the commandbutton. (Just doubleclick on that commandbutton from the control toolbox toolbar and paste that code in.) Adjust the worksheet names and the autoshape names (and the worksheets that hold them???). Dave Y wrote: Hello, I have a workbook that is stored in 2 seperate places on the network. One of the copies satys as is and the other copy is placed in a different directory which is meant to be a view only copy. There are multiple worksheets within this workbook along with 2 AutoShapes that I do not want to be visible in the view only copy. I know how to hide the worksheets that I do not want to visible using VBA but I still have 2 issues that I need help with: 1. How can I hide the 2 AutoShapes in the view only copy using VBA. Both AutoShapes are placed on the same worksheet. 2. I would like to create a button (ActiveX Control) that I would use to hide the worksheets and the AutoShapes that I do not want to be visible in one click. Can I assign permissions to that button so that only I can make the contol perform its function? Any help, suggestions, or useful links will be greatly appreciated. Thank you. Dave Y -- Dave Peterson |
#3
|
|||
|
|||
Ps. Remember to lock the project
Inside the VBE, tools|VBAProject properties|protection tab. But even this protection can be bypassed pretty quickly. Dave Y wrote: Hello, I have a workbook that is stored in 2 seperate places on the network. One of the copies satys as is and the other copy is placed in a different directory which is meant to be a view only copy. There are multiple worksheets within this workbook along with 2 AutoShapes that I do not want to be visible in the view only copy. I know how to hide the worksheets that I do not want to visible using VBA but I still have 2 issues that I need help with: 1. How can I hide the 2 AutoShapes in the view only copy using VBA. Both AutoShapes are placed on the same worksheet. 2. I would like to create a button (ActiveX Control) that I would use to hide the worksheets and the AutoShapes that I do not want to be visible in one click. Can I assign permissions to that button so that only I can make the contol perform its function? Any help, suggestions, or useful links will be greatly appreciated. Thank you. Dave Y -- Dave Peterson |
#4
|
|||
|
|||
Hi Dave,
Thank you for the code. I will try it on Monday when I'm back at my desk. I'll post again to let you know how things worked out or if I have more questions. I appreciate your assistance. Dave Y "Dave Peterson" wrote: Ps. Remember to lock the project Inside the VBE, tools|VBAProject properties|protection tab. But even this protection can be bypassed pretty quickly. Dave Y wrote: Hello, I have a workbook that is stored in 2 seperate places on the network. One of the copies satys as is and the other copy is placed in a different directory which is meant to be a view only copy. There are multiple worksheets within this workbook along with 2 AutoShapes that I do not want to be visible in the view only copy. I know how to hide the worksheets that I do not want to visible using VBA but I still have 2 issues that I need help with: 1. How can I hide the 2 AutoShapes in the view only copy using VBA. Both AutoShapes are placed on the same worksheet. 2. I would like to create a button (ActiveX Control) that I would use to hide the worksheets and the AutoShapes that I do not want to be visible in one click. Can I assign permissions to that button so that only I can make the contol perform its function? Any help, suggestions, or useful links will be greatly appreciated. Thank you. Dave Y -- Dave Peterson |
#5
|
|||
|
|||
Hey Dave Peterson,
I just tried out your code and it worked great! Thank you. I had to tweak it a little; for some reason I kept receving an error stating it could not find the shape when I ran the code. I only had 2 autoshapes on my test worksheet and I tried everyname for the shapes I could think of but I kept receiving the error. I eneded up using the following line of code which hides all autoshapes: ActiveWorkbook.DisplayDrawingObjects = xlHide That hides all the autoshapes on the sheet which is not exactly what I was looking to do but it will work for now. The password functionality and the hiding of the worksheets works perfectly. Thanks again for you help, I really appreciate it. Dave Y "Dave Y" wrote: Hi Dave, Thank you for the code. I will try it on Monday when I'm back at my desk. I'll post again to let you know how things worked out or if I have more questions. I appreciate your assistance. Dave Y "Dave Peterson" wrote: Ps. Remember to lock the project Inside the VBE, tools|VBAProject properties|protection tab. But even this protection can be bypassed pretty quickly. Dave Y wrote: Hello, I have a workbook that is stored in 2 seperate places on the network. One of the copies satys as is and the other copy is placed in a different directory which is meant to be a view only copy. There are multiple worksheets within this workbook along with 2 AutoShapes that I do not want to be visible in the view only copy. I know how to hide the worksheets that I do not want to visible using VBA but I still have 2 issues that I need help with: 1. How can I hide the 2 AutoShapes in the view only copy using VBA. Both AutoShapes are placed on the same worksheet. 2. I would like to create a button (ActiveX Control) that I would use to hide the worksheets and the AutoShapes that I do not want to be visible in one click. Can I assign permissions to that button so that only I can make the contol perform its function? Any help, suggestions, or useful links will be greatly appreciated. Thank you. Dave Y -- Dave Peterson |
#6
|
|||
|
|||
If you want to try again, I'd bet it was that you weren't using the correct name
for the shape. If you select that shape (or rightclick on it), you should see the name in the namebox -- to the left of the formula bar. If that doesn't help, you could run a little macro: Option Explicit Sub testme01() Dim iCtr As Long With ActiveSheet For iCtr = 1 To .Shapes.Count MsgBox iCtr & vbLf & .Shapes(iCtr).Name _ & vbLf & .Shapes(iCtr).TopLeftCell.Address Next iCtr End With End Sub Dave Y wrote: Hey Dave Peterson, I just tried out your code and it worked great! Thank you. I had to tweak it a little; for some reason I kept receving an error stating it could not find the shape when I ran the code. I only had 2 autoshapes on my test worksheet and I tried everyname for the shapes I could think of but I kept receiving the error. I eneded up using the following line of code which hides all autoshapes: ActiveWorkbook.DisplayDrawingObjects = xlHide That hides all the autoshapes on the sheet which is not exactly what I was looking to do but it will work for now. The password functionality and the hiding of the worksheets works perfectly. Thanks again for you help, I really appreciate it. Dave Y "Dave Y" wrote: Hi Dave, Thank you for the code. I will try it on Monday when I'm back at my desk. I'll post again to let you know how things worked out or if I have more questions. I appreciate your assistance. Dave Y "Dave Peterson" wrote: Ps. Remember to lock the project Inside the VBE, tools|VBAProject properties|protection tab. But even this protection can be bypassed pretty quickly. Dave Y wrote: Hello, I have a workbook that is stored in 2 seperate places on the network. One of the copies satys as is and the other copy is placed in a different directory which is meant to be a view only copy. There are multiple worksheets within this workbook along with 2 AutoShapes that I do not want to be visible in the view only copy. I know how to hide the worksheets that I do not want to visible using VBA but I still have 2 issues that I need help with: 1. How can I hide the 2 AutoShapes in the view only copy using VBA. Both AutoShapes are placed on the same worksheet. 2. I would like to create a button (ActiveX Control) that I would use to hide the worksheets and the AutoShapes that I do not want to be visible in one click. Can I assign permissions to that button so that only I can make the contol perform its function? Any help, suggestions, or useful links will be greatly appreciated. Thank you. Dave Y -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I automatically hide columns in a worksheet based on a cell value? | Excel Worksheet Functions | |||
I faxed an excel worksheet and now cannot print? | Excel Worksheet Functions | |||
How do I replace a worksheet with another worksheet in excel | Excel Worksheet Functions | |||
Excel - if cells = 0, how to conditionally hide rows in chart | Excel Worksheet Functions | |||
Conditional Hide function for Excel | Excel Worksheet Functions |