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
’
’
Sub Button1_Click()
If (Cells(14, 9).Value <> “”) Then
ActiveSheet.Unprotect (“letmein”)
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
Else
Beep
Beep
End If
End Sub
If anyone could help me it would be greatly appreciated
Cheers
Chrisso
Model: Powermac G%
AppleScript: 2.1.2
Browser: Firefox 3.0.3
Operating System: Mac OS X (10.5)
Thank you.
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
else
display dialog "No date in A14"
end if
end tell
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 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.