Hi can anyone help me - I have a worksheet from Excel X that has a VBA script that lets you click a button that prints and resets a timesheet every fortnight
I could tinker with VBA or applescript but converting this is way above me
This is the script in VBA
’ Button1_Click Macro
If (Cells(14, 9).Value <> “”) Then
ActiveWindow.SelectedSheets.PrintOut Copies:=1 'print out the sheet
Cells(2, 2).Value = 1 + Cells(14, 1).Value 'start the next period from the day after the end of the current period
Cells(2, 8).Value = Cells(16, 9).Value 'balance carried forward to next period
Range(Cells(5, 2), Cells(14, 3)).Value = “” 'clear current entries
Range(Cells(5, 5), Cells(14, 7)).Value = “” 'clear current entries
ActiveSheet.Protect password:=“letmein”, DrawingObjects:=True, Contents:=True, Scenarios:=True
If anyone could help me it would be greatly appreciated
Model: Powermac G%
Browser: Firefox 3.0.3
Operating System: Mac OS X (10.5)
I’m also learning AppleScript from a VBA background and this was a good practice code for me.
The MicroSoft website, where I got these Excel2004 AppleScript commands, has a section on VBA to AppleScript.
If more experienced scripters have comments, I would appreciate them.
(Is my variable oneDay really needed?)
I couldn’t make the beep, so I put up a dialog box instead.
tell application "Microsoft Excel"
set oneday to 24 * 60 * 60
set previousDay to value of range "A14"
if class of previousDay as text = "date" then
print out active sheet
unprotect active sheet password "password"
set value of range "B2" to previousDay + oneday
set previousBalance to value of range "i16"
set value of range "H2" to previousBalance
clear contents range "B5:C14, E5:F14"
protect worksheet active sheet password "password" with drawing objects, worksheet contents and scenarios
display dialog "No date in A14"
Thanks for such a quick reply - I gave it a quick run before I go It prints and clears the data from some cells but leaves other and does not change to the new date
I will be away for a couple of days and give it a closer look but initially it is great
no, 24 * 60 * 60 is the same as days
Thanks for the tips.
Thanks very much for the conversion - with a couple of minor tweaks it seems to be working perfectly
One query The original VBA script was assigned to a button - any suggestions as to how I might reassign script to the button
No clue as to how to assign it to a button.
Does 2008 have buttons that can be put on a spread sheet? I don’t see why it would? Other controls from the Forms menu would have a function, but, since VBA is not supported, a command button has no function.
I think that one can put icons on the dock or top bar that will call an AppleScript. perhaps someone else on the board knows how to do that.