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

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

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

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

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

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