Sundry macro-type questions

ACommandLineKindaGuy

Active Member
Joined
May 11, 2002
Messages
378
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
To get an idea on how various operations occur you might want to record some marco's and then view the code.
 
Upvote 0
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.
 
Upvote 0
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

Code:
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
 
Upvote 0
Not to argue with an MVP (especially since I have no access to Excel in this hotel)

Woudl something like this work?

<pre>

ActiveSheet.UsedRange.Select
Selection.Locked = False
Selection.ClearContents</pre>

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

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top