Greetings!
I am new to AppleScript, and i was recommended to this forum. So, I thank you in advance your help!
I created a program/workbook in Excel 2011 using VBA macros. The users of the program consequently use Excel 2008 which does not utilize macros. I have a few buttons on the spreadsheet that perform certain functions. Can someone help guide me to where to start with these? Again, I appreciate any help I can get!!
1.) How do I attach the following code to a button using AppleScript, I’d prefer if it’s the same button as the Macro-button.
2.) If I send this file to another user(with Excel 2008), what would they have to do in oder for the program to work?
My code::
'Check to see if worksheet exits
Public Function SheetExists(ByVal SheetName As String) As Boolean
Dim i As Integer
With ActiveWorkbook
For i = 1 To Sheets.Count
If Sheets(i).Name = SheetName Then
SheetExists = True
Exit Function
End If
Next
SheetExists = False
End With
End Sub
'Send to Summary page
Sub SummaryCells()
Dim ws As Worksheet
Sheets(“Summary YTD”).Select
Sheets(“Data”).Visible = False
For Each ws In Worksheets
If ws.Name Like “Jan” Then
Sheets(“Summary YTD”).Range(“B3:B7”).Value = Sheets(“Jan”).Range(“B3:B7”).Value
Sheets(“Summary YTD”).Range(“B98”).Value = Application.WorksheetFunction.Sum(Sheets(“Jan”).Range(“B98:B102”))
End If
If ws.Name Like “Feb” Then
Sheets(“Summary YTD”).Range(“B3:B7”).Value = Sheets(“Feb”).Range(“B3:B7”).Value
Sheets(“Summary YTD”).Range(“B98”).Value = Application.WorksheetFunction.Sum(Sheets(“Feb”).Range(“B98:B102”))
End If
…
'This line of code continues through December.
End Sub
Thanks Yvan!!
Those sites provided great tutorials and insights that are helping me in other areas! However, there are two issues I’m not finding answers on:
-
Making a hidden template sheet visible for editing, then hide again. Specifically, In Mac 2011, I’m able to unhide a template, copy it, change the name, and hide it again.
-
Making a cell(range) a pseudo button since buttons/Macros do not work in Excel 2008. I don’t want the AppleScript to perform these functions automatically. Any help would be greatly appreciated!!
Thank you in advance!
I am travelling at the moment without a laptop, but if memory serves me right, you can create a button in Ms excel, assign it to a “MacScript()” command which you need to link to your script, and it will run. I can’t remember the exact syntax off the top of my head, but this is the only way, though i remember it worked for me.
Look up the command and you should find examples.
TimMurphy,
Thanks again for looking into my post! I’ve found a way to attach the button to the AppleScript. And it works perfect for my computer…However, I am sharing this excel document with other users. The AppleScript references my hard drive name. Is there a way to change the name to whomever uses it? So that they can utilize the scripts on their Macs?? They share the Dropbox folder. Script is below.
Sub Button1_Click()
Dim scriptToRun As String
scriptToRun = “set myScript to ““Macintosh HD:Users:MyUserName:Dropbox:Mac Excel Scripts:NewWorksheet-2008.scpt”” as alias”
scriptToRun = scriptToRun & vbCr & “run script myScript”
MacScript (scriptToRun)
End Sub
I want to replace myUsername with theirUsername. If there’s a better way to do this, please advise. Thanks again for the help!! This forum has been awesome!
For windows this is accomplised by using the Environ:
"\Users" & Environ(“Username”) & "\Dropbox\Mac Excel Scripts\NewWorksheet-2008.scpt"
-E
I am sorry, but I can’t really help with your last question…