PDA

View Full Version : MACROS



josephm
06-02-2008, 06:30 AM
hello i am new to this forum. i found some help to my problem last week and i was so impressed that i decided to join. good job guys.

i have a custom button which will run a macro on sheet two of the work book in question. however i want the macro to work while the sheet is protected.:banghead:

i have two bits of code.

the first one i found via google which will tell the macro to unprotect the sheet do the action then re-protect it.

Sub MyMacro()

Sheet1.Unprotect Password:="Secret"
'Place macro code here
Sheet1.Protect Password:="Secret"
End Sub


The next piece of code is what i was using to run the action but it doesn't facilitate protected sheets.

Public Sub StartFilter()



If Not ActiveSheet.AutoFilterMode Then

ActiveSheet.Range("A1").AutoFilter

End If

in the first piece of code it says place my macro code here, but i am unsure how to mesh the two piece of code together. what i am trying to do overall is have the macro run on a protected sheet when i click the button which will run the macro.

Bob Phillips
06-02-2008, 07:04 AM
Sub MyMacro()

With Sheet1
.Unprotect Password:="Secret"
If Not .AutoFilterMode Then

.Range("A1").AutoFilter

End If
.Protect Password:="Secret"
End With
End Sub

josephm
06-02-2008, 07:13 AM
Thank you i did something similar but as i am a noob when it comes to any programming. i had removed the End sub from the last piece of code and put the rest where it said 'place your code here'

the code above works but i cant use the drop down arrow will i have to include the protect user interface code to allow the user to use the autofilter not only turn it on?

i tried the following modification but i got an error say that the variable was not defined.

Sub TurnOnAutoFilter()

With Sheet2
.Unprotect Password:="Secret"
UserInterFaceOnly = True
If Not .AutoFilterMode Then

.Range("A1").AutoFilter

End If
.Protect Password:="Secret"
UserInterFaceOnly = True
End With
End Sub

the bolded text is where i got the error.

josephm
06-02-2008, 07:28 AM
Question i tried to do the samething with the Delete Duplicates In Column A Code which i found on this forum

i tired the following...

Sub DeleteMyDups()

With Sheet2
.Unprotect Password:="Secret"
UserInterFaceOnly = True
Dim x As Long
Dim LastRow As Long

LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x
.Protect Password:="Secret"
UserInterFaceOnly = True
End With
End Sub

it attached it to the button like i did for the autofilter situation but i didnt work with the duplicate button i setup.
this remove duplicate code must work only for sheet2 colums A and B.

Bob Phillips
06-02-2008, 07:45 AM
UserInterfaceOnly applies to Protect, not unprotect, and is a property of the method.

josephm
06-02-2008, 08:12 AM
with that said how will i allow the user to use the auto filter?

i want the user to turn on the auto filter and use it while the sheet is protected.

so how can i do that using the following code.

Sub MyAutoFilterMacro()

With Sheet2
.Unprotect Password:="Secret"
If Not .AutoFilterMode Then

.Range("A1").AutoFilter

End If
.Protect Password:="Secret"
End With
End Sub


i also tried the following method but when i assigned it to a button it did nothing.

Private Sub Workbook_Open()
Sheet2.Protect password:="test", DrawingObjects:=True, _
contents:=True, Scenarios:=True, _
userinterfaceonly:=True
Sheet1.EnableAutoFilter = True
End Sub

josephm
06-02-2008, 09:17 AM
i found some code that works.

Private Sub Workbook_Open()
Sheet2.Protect password:="test", DrawingObjects:=True, _
contents:=True, Scenarios:=True, _
userinterfaceonly:=True
Sheet2.EnableAutoFilter = True
End Sub

What i am asking is if i save the work book as a different name will the macro still work.?

also how can i use the following code provided by DRJ on the same protected sheet where i have a custom button running the macro when clicked. drj'S code is as follows....

Option Explicit

Sub DeleteDups()

Dim x As Long
Dim LastRow As Long

LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x

End Sub

Bob Phillips
06-02-2008, 09:31 AM
Yes it will if in the saved workbook.



Sub DeleteMyDups()

With Sheet2
.Unprotect Password:="Secret"
Dim x As Long
Dim LastRow As Long

LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.CountIf(.Range("A1:A" & x), .Range("A" & x).Text) > 1 Then
.Rows(x).Delete
End If
Next x
.Protect Password:="Secret", UserInterFaceOnly:= True
End With
End Sub

josephm
06-02-2008, 10:47 AM
thank you

josephm
06-02-2008, 01:00 PM
what do you mean by

"Yes it will if in the saved workbook."

This is the process i go through to create a macro, with the xcel file already opened

1) i go to the visual basic editor
2) then when the editor opens, i double click on 'this workbook'
3) i enter the code
4) save
5) close the editor
6) go back to the main sheet
7) assign the macro to a button
8) save
9)run

then if i rename the workbook to a new name and try to re-run the macro

i get the following error

THE MACRO "FILE_NAME!'MACRO_NAME' CAN NOT BE FOUND