Sundry macro-type questions

[ACommandLineKindaGuy]

I'd appreciate any help in automating a workbook with a number of sheets where user input data is unlocked/unhidded, and each sheet protected. What I'd like to do is develop macros that:
1. Delete user input in a particular sheet only by deleting all cells that are unlocked/unhidden by pressing a button in that sheet.
2. Unprotect all sheets, select all sheets, select all cells of all sheets, right-click copy, and right-click paste special-values so that all sectret-type formulas are replaced by the value they calculate. I can then save the workbook under a new name and distribute it to others.
3. Select active sheets (what I call active sheets are sheets with a particular named cell >0 (eg SBC!AA6 = SBCCost = $12,430,000)
and print them.

Thanks in advance for any consideration of these questions. Back in the days of 1-2-3 v. 2.4 I could do this kind of stuff--I guess this ole dawg isn't up to learning VB--or at least is intimidated by it!

[Nimrod]

To get an idea on how various operations occur you might want to record some marco's and then view the code.

[ACommandLineKindaGuy]

I agree that recording macros is the best way--but then how do I deal with selecting all unlocked cells in a spreadsheet and clearing their contents?--It seems to me that recording a macro is fine when you know what you want to do in advance of performing the operation. Believe me, I've fiddled all over the place with recorded macros and then trying to change stuff around.

[Jay Petrulis]

On 2002-05-12 19:29, ACommandLineKindaGuy wrote:
I'd appreciate any help in automating a workbook with a number of sheets where user input data is unlocked/unhidded, and each sheet protected. What I'd like to do is develop macros that:
1. Delete user input in a particular sheet only by deleting all cells that are unlocked/unhidden by pressing a button in that sheet.
2. Unprotect all sheets, select all sheets, select all cells of all sheets, right-click copy, and right-click paste special-values so that all sectret-type formulas are replaced by the value they calculate. I can then save the workbook under a new name and distribute it to others.
3. Select active sheets (what I call active sheets are sheets with a particular named cell >0 (eg SBC!AA6 = SBCCost = $12,430,000)
and print them.

Thanks in advance for any consideration of these questions. Back in the days of 1-2-3 v. 2.4 I could do this kind of stuff--I guess this ole dawg isn't up to learning VB--or at least is intimidated by it!

1. Try something like the following (hard code the name of the sheet if needed. You may also need to supply a password with the .unprotect and .protect commands

Sub tester()
Dim UsedCell As Range
    With ActiveSheet
    .Unprotect
    .UsedRange
        For Each UsedCell In ActiveSheet.UsedRange
            If UsedCell.Locked = False Then
                UsedCell.ClearContents
            End If
        Next UsedCell
    .Protect
    End With
End Sub

2. Do the same as above, but throw it in a loop

Dim wks as worksheet

For each wks in thisworkbook.worksheets
'''your code here
Next wks

3. Would be done in your code for #2 above

If Wks.Range("A1")>0 then wks.printout

Pseudo code suppliced in 2 and 3 above, so please test some things and try it out.

Bye,
Jay

[ACommandLineKindaGuy]

Thanks Jay, I'll try that

[Mark O'Brien]

Not to argue with an MVP (especially since I have no access to Excel in this hotel)

Woudl something like this work?

ActiveSheet.UsedRange.Select

Selection.Locked = False

Selection.ClearContents

This would be faster than Jay's loop. (this is neither here nor there, because Jay's code should work)