Using a macro question.

Can you execute a macro from inside a formula?  IF(A1="Y",execute.macro,"")

Thanks 


0
Adam
12/14/2005 11:43:12 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
627 Views

Similar Articles

[PageSpeed] 22

Adam, not with a formula, but you can use a worksheet change event like
this, put in sheet code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$A$1" And UCase(Target.Value) = "Y" Then
'put your code here
End If
End Sub


-- 
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
news:kN2dnVbNjJ0oMD3enZ2dnUVZ_sGdnZ2d@comcast.com...
> Can you execute a macro from inside a formula?
IF(A1="Y",execute.macro,"")
>
> Thanks
>
>


0
12/15/2005 12:16:04 AM
You can call a UDF, which is a macro that returns a result, but it can only
return a result, it cannot change other cells, or any worksheet attributes.

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
news:kN2dnVbNjJ0oMD3enZ2dnUVZ_sGdnZ2d@comcast.com...
> Can you execute a macro from inside a formula?
IF(A1="Y",execute.macro,"")
>
> Thanks
>
>


0
bob.phillips1 (6510)
12/15/2005 12:18:30 AM
No.

Biff

"Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message 
news:kN2dnVbNjJ0oMD3enZ2dnUVZ_sGdnZ2d@comcast.com...
> Can you execute a macro from inside a formula? 
> IF(A1="Y",execute.macro,"")
>
> Thanks
> 


0
biffinpitt (3172)
12/15/2005 12:19:49 AM
what would I need to change to allow that UDF to be used in any cell?

This is what I want to run:

Sub ROll2D6()
Dim myCell As Range
Dim Sides As Integer
Dim Dies As Integer
Dim i As Integer
Dim myTemp As Integer

Sides = 6
Dies = 2
For Each myCell In Selection
    Randomize
    myTemp = 0
    For i = 1 To Dies
    myTemp = myTemp + Application.RoundUp(Rnd() * Sides, 0)
    Next i
    myCell.Value = myTemp
Next myCell
End Sub


"Paul B" <to_much_spam_to_list@nospam.com> wrote in message 
news:us11Y0QAGHA.3140@TK2MSFTNGP14.phx.gbl...
> Adam, not with a formula, but you can use a worksheet change event like
> this, put in sheet code
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Count > 1 Then Exit Sub
> If Target.Address = "$A$1" And UCase(Target.Value) = "Y" Then
> 'put your code here
> End If
> End Sub
>
>
> -- 
> Paul B
> Always backup your data before trying something new
> Please post any response to the newsgroups so others can benefit from it
> Feedback on answers is always appreciated!
> Using Excel 2002 & 2003
>
> "Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
> news:kN2dnVbNjJ0oMD3enZ2dnUVZ_sGdnZ2d@comcast.com...
>> Can you execute a macro from inside a formula?
> IF(A1="Y",execute.macro,"")
>>
>> Thanks
>>
>>
>
> 


0
Adam
12/15/2005 2:16:11 AM
That cannot be a UDF as you are trying to write to many cells, a UDF only
returns a result.

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
news:c8GdnXw7YcQNTD3enZ2dnUVZ_tCdnZ2d@comcast.com...
> what would I need to change to allow that UDF to be used in any cell?
>
> This is what I want to run:
>
> Sub ROll2D6()
> Dim myCell As Range
> Dim Sides As Integer
> Dim Dies As Integer
> Dim i As Integer
> Dim myTemp As Integer
>
> Sides = 6
> Dies = 2
> For Each myCell In Selection
>     Randomize
>     myTemp = 0
>     For i = 1 To Dies
>     myTemp = myTemp + Application.RoundUp(Rnd() * Sides, 0)
>     Next i
>     myCell.Value = myTemp
> Next myCell
> End Sub
>
>
> "Paul B" <to_much_spam_to_list@nospam.com> wrote in message
> news:us11Y0QAGHA.3140@TK2MSFTNGP14.phx.gbl...
> > Adam, not with a formula, but you can use a worksheet change event like
> > this, put in sheet code
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Count > 1 Then Exit Sub
> > If Target.Address = "$A$1" And UCase(Target.Value) = "Y" Then
> > 'put your code here
> > End If
> > End Sub
> >
> >
> > -- 
> > Paul B
> > Always backup your data before trying something new
> > Please post any response to the newsgroups so others can benefit from it
> > Feedback on answers is always appreciated!
> > Using Excel 2002 & 2003
> >
> > "Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
> > news:kN2dnVbNjJ0oMD3enZ2dnUVZ_sGdnZ2d@comcast.com...
> >> Can you execute a macro from inside a formula?
> > IF(A1="Y",execute.macro,"")
> >>
> >> Thanks
> >>
> >>
> >
> >
>
>


0
bob.phillips1 (6510)
12/15/2005 8:46:55 AM
The way the macro works as currently written, it can return to multiple 
cells; but in this instance, I just need it to return either the sum of 2 
randomly generated numbers between 1 and 6 (simulating a dice roll), or a 
single randomly generated number between 1 and 6, and then have the cell be 
stable after that is done (not constantly recalculating with every chnage 
made to the spreadsheet).


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message 
news:uBokaQVAGHA.264@tk2msftngp13.phx.gbl...
> That cannot be a UDF as you are trying to write to many cells, a UDF only
> returns a result.
>
> -- 
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
> news:c8GdnXw7YcQNTD3enZ2dnUVZ_tCdnZ2d@comcast.com...
>> what would I need to change to allow that UDF to be used in any cell?
>>
>> This is what I want to run:
>>
>> Sub ROll2D6()
>> Dim myCell As Range
>> Dim Sides As Integer
>> Dim Dies As Integer
>> Dim i As Integer
>> Dim myTemp As Integer
>>
>> Sides = 6
>> Dies = 2
>> For Each myCell In Selection
>>     Randomize
>>     myTemp = 0
>>     For i = 1 To Dies
>>     myTemp = myTemp + Application.RoundUp(Rnd() * Sides, 0)
>>     Next i
>>     myCell.Value = myTemp
>> Next myCell
>> End Sub
>>
>>
>> "Paul B" <to_much_spam_to_list@nospam.com> wrote in message
>> news:us11Y0QAGHA.3140@TK2MSFTNGP14.phx.gbl...
>> > Adam, not with a formula, but you can use a worksheet change event like
>> > this, put in sheet code
>> >
>> > Private Sub Worksheet_Change(ByVal Target As Range)
>> > If Target.Count > 1 Then Exit Sub
>> > If Target.Address = "$A$1" And UCase(Target.Value) = "Y" Then
>> > 'put your code here
>> > End If
>> > End Sub
>> >
>> >
>> > -- 
>> > Paul B
>> > Always backup your data before trying something new
>> > Please post any response to the newsgroups so others can benefit from 
>> > it
>> > Feedback on answers is always appreciated!
>> > Using Excel 2002 & 2003
>> >
>> > "Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
>> > news:kN2dnVbNjJ0oMD3enZ2dnUVZ_sGdnZ2d@comcast.com...
>> >> Can you execute a macro from inside a formula?
>> > IF(A1="Y",execute.macro,"")
>> >>
>> >> Thanks
>> >>
>> >>
>> >
>> >
>>
>>
>
> 


0
Adam
12/15/2005 9:55:03 PM
Reply:

Similar Artilces:

Trapping a value to use in my form
I am quite new to this and need some help. I have a form with a record source of a query. In the query I have the criteria set to ask the user a question in a popup box which searches a table for that input which is then reported back to the form to view EmployeeNumberQuery is as follows: Field: Employee Table: Employee_Number Criteria: [Enter the Employee Number] <-this is the value I want to capture and display in the form This query also has other fields that are reported back to the form that match the entered value The Employee_Listing_Table is as follows: Form Hea...

Standard Cost Roll up (using Standard Cost Changes)
Hi all, We are finding that when we change a component on a sub-assembly, the resulting roll-up is affecting other sub-assemblies that share the same parents but not the changed component. For example: We changed the cost a component item for sub-assembly 25-0051 and roll up the sub-assembly. This sub-assembly reports to our top level 30-0100 and 30-0101. The roll-up then went and changed ALL related assemblies that share the top level 30-0100 and 30-0101. These assemblies DO NOT share the changed component but do have other proposed changes since last roll/revalue. These proposed...

Invalid use of Null
Hi, M getting error (Invalid use of Null) with crosstab where i have used left outer join. I tried different ways to get rid of error message but it follows all the time. I tried using "Nz" "IIF" but didnt work. Please help if someone can help me on my below query. TRANSFORM Sum(IIf([ANR]>0,[ANR],0)) AS Expr1 SELECT report4.country FROM report4 LEFT JOIN REPORT4_ACTUALS ON (report4.PL = REPORT4_ACTUALS.PL) GROUP BY report4.country PIVOT REPORT4_ACTUALS.Expr2 Thanks in advance, Vikram Hi, Try first creating a normal select query that returns the records which you...

MX record question #2
I have one windows 2003 and one exchange on the same server need to receive the email from Internet. Did I need to setup a MX record in my own DNS server point to my own server. Or only need to add a MX record in the ISP to point to my server Internet IP address ? or both needed? thanks in advice. lamlam wrote: > I have one windows 2003 and one exchange on the same server need to > receive the email from Internet. Did I need to setup a MX record in > my own DNS server point to my own server. Or only need to add a MX > record in the ISP to point to my server Internet IP addre...

Add Record to Subform using a Listbox
I have a Form with a Subform. The Form contains a multi-select List Box for selection of records to add to the Subform. I can’t seem to get the code right to accomplish this. Any help would be very much appreciated. FrmEvents (contains List Box: “lstDefs” & subform: “frmSubEventDef”) Record Source: q_frm_event Primary key: EventNo lstDefs (Unbound) Field 1: DefNo Field 2: Defintion Multi-Select: Extended frmSubEventDef Record Source: q_frm_event_defs Field 1: EventNo Field 2: DefNo Link Child Fields: EventNo Link Master Fields: EventNo On selection of specific row(s) in lstDefs, th...

Hyperlink Macro to 2,500 PDF files?
Fellow Forum Members, I have 2,500 PDF ebooks on a variety of subjects all contained in one folder named "Ebooks". I already have all the 2,500 ebook titles inside Excel in Column A, Sheet 1. Now I want to hyperlink each title to the actual PDF file and doing this hyperlink operation 2,500 times is something I want to avoid like the plague. Can anyone out there please help me in developing a macro that would do this hyperlink operation automatically 2,500 times? I'm not a macro expert, but is it not possible to make Excel automically go to the same Ebook folder 2,500 times a...

outlook on a network question
Hi, I have outlook as bundled with office 2000 on our pc's at home. What I'd like to do is to have the messages and address book one once pc, that is accessed from both. I did have this working a couple of years back, but we changed the setup. How can I tell one of the installations to use the data as stored on the other pc? I do know that you can only run outlook on one pc at a time using this facility. Thanks Is Outlook 2000 configured in Corporate/Workgroup or Internet Mail Only mode? (See Help | About Microsoft Outlook. Second or third line should state the mode Out...

2003 to 2000 security question
To make a long story short I am trying to work with our Quality department to get all of our quality records available on a shared drive. To do this we need to make sure that they cannot be modified. Currently Office 2003 (specifically Excel) has the security needed to prevent users from copying the form to another Excel worksheet or workbook and making modifications. However, if the same file is opened in an earlier version of Excel, the same security does not function and the cells can be copied and then modified. Is there a way i can prevent this from happening with out having to ...

Exchange2003 LIMIT RAM use
How do I do that? We have 2gigs RAM and I am tired of seeing the store.exe messages. The 4.5 was easily set, 2003 I can not find it. Thanks What store.exe messages? Store.exe is designed to use memory, otherwise there's no point in having memory in the server. -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "BrianMultiLanguage" <BrianMultiLanguage@discussions.microsoft.com> wrote in message news:473A3C72-1857-4999-A9C9-DABC6FA0F169@microsoft.com... > How do I do that? We have 2gigs RAM and I am tired of seeing ...

Share a Calendar using WEB CLIENT
is it possible to allow other users to view you calendar on the web client and NOT OUTLOOK? This actually not possible, since the CRM Server get's your network credentials to decide which calendar to show... The only option is to give the other user's credentials in the URL when starting CRM. ("http://<user>:<password>@<crmserver>"). However, this means you have to specify another user's password in a url... And.. in the latest security updates for IE, this possibility is disabled, which can be enabled again through register settings... Good Lu...

Using publisher 2007, how do I reformat a PUB flyer to jpg format
I just started using PUB 2007 and have this question -- pls help. Mny thx, Gil Rosoff, San Mateo, CA File, save as, Files of type, scroll down to the .jpg options. -- Mary Sauer http://msauer.mvps.org/ "Gil Rosoff" <Gil Rosoff@discussions.microsoft.com> wrote in message news:B70E8398-1BD2-44A2-AF3E-20DB8D7A646C@microsoft.com... >I just started using PUB 2007 and have this question -- pls help. > Mny thx, Gil Rosoff, San Mateo, CA Why use a .jpg? Why not a .pdf? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dum...

I want to update a status reason in an email activity using Workfl
Hi, In 4.0 I am doing a workflow that changes status reason in an email activity .. I want to update it on demand, ie I am going to open an email and run the Workflow to change the status reason. I created a workflow for email entity and enable only on demand, I then added the step for updating EMail. When I click Set Properties Email Form is blank. I then change the only Status Reason to the some value which is allowed in this variable. Do I need to care about other values on this Form. At present I change the value only for status reason. But this workflow does nothing when I run on...

Money 2003 Question #2
My computer froze and shut down with Microsoft money open. When I reboot and tried to open file it gives me an error "Money has detected that you did not close the file before exiting. Money now needs to verify the information in your file. Chose Yes. When I chose Yes, it says "repairing file" and then gives me the error that the password I entered was incorrect. Can anyone tell me how to prevent this situation? It's happened several times and takes me a long time to update my file from a back up version. You might want to try renaming the client.xml file. I...

wasy macro question
I used the macro recorder to get the code to resize columns on a worksheet Cells.Select Range("A1766").Activate Cells.EntireColumn.AutoFit What I don't understand in the Range. It's seems to be referring to a single cell. Could someone explain to a VBA newbie? I'm importing a text file and I want to resize the columns after import. I have the import working just need this one last thing. The worksheet will always have the same number of columns but the number of rows may vary will the above code still work? gls858 gls858 wrote: > I used the macro reco...

Text Formula Question
In COL A, I have a list of 2 and 3 digit charactors. I would like to seperate each string into the three adjacent columns. KQ AJs TT 89s KQ K Q AJs A J s TT T T 89s 8 9 s I know that I can use the Left function for the first col, but cant figure out how to get just the middle or third letters. Thanks in advance, Andrew Check your last post! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------...

KBV- vlookup question
In the vlookup formula, the range entered includes the columns that need to be searched for matching or similar values and the range also includes the column from which the corresponding value has to be returned. What if there are 2 or more columns within that range that match the lookup value? How does excel handle that? According to Help for Excel 2003, VLOOKUP "searches for a value in the first column of a table array." It only searches in that column for the lookup_value argument. -Ryan KBV wrote: > In the vlookup formula, the range entered includes the columns that ne...

Using Min to Calculate across a table
I am attempting to massage data so I can see what the maximum number of pieces I can produce based off of various components. My query to figure out how many of each component is available per unit is complete and the output is: Part_Number, MaxComponent1, MaxComponent2,MaxComponent3, MaxComponent4 Does anyone have any suggestions as to how I could find the MIN of the components and group by part number? Cheers See the MinOfList() function here: http://allenbrowne.com/func-09.html A better solution would be to create a related table with many records for the combinations that are va...

How to Edit the Macro that exists behind an existing Command Butto
Hi There, Hoping someone can help me out ... I have inherited the task of managing an Access 2007 application. Within this application I have an Access 2007 form that has a Command Button. I'm not sure how this Command Button was created (Macro, Query, etc.) but I do know what it does. I would like to edit the Query/Macro this Command Button executes. I'd rather not edit the VBA coding as I am a VBA Beginner. Can you pleae let me know how I can edit the Command Button's Query/Macro. Thank you, Jason A command button can run a macro or a VB event proce...

hwnd question
hwnd hA was created in process A hwnd hB was created in process B Q: is it legal for A to parent B ? Yes it is legal. But keep in mind these points. One thing to remember is that the child window sends message back to it parent. When your child window sends a message to it's parent window, the thread that the child window is in will block until the message is processed by the parent in a different thread. And be careful to destroy the thread with the child window before you destroy the parent window! Ali R. "anon" <someone@internet.com> wrote in message news:Oz3%23...

assing macro to an an image, shape, text in Word 2007
I would like to run one of my macros when either an image, or a shape, or a text is clicked in Word 2007 document. Thanks, Use an ActiveX control, as they have a _Click event. Lev wrote: >I would like to run one of my macros when either an image, or a shape, or a >text is clicked in Word 2007 document. >Thanks, -- Gerry Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/word-programming/201004/1 Another method is to insert a MacroButton field and paste the image into the field code where the display text would be. See http://www.word...

Need to verify cell location before running a macro, how?
I am requesting users to click on a cell in column B (in which they are actually selecting a row of reference information), then to click on an icon which launches a macro that runs relevant to the row selected in column B. I would like Excel to verify that the selected cell is actually in column B prior to running the macro. If a cell in column B is not selected, I want to present a pop up message reminding them to select a cell in column B. Please help! If ActiveCell.Column <> 2 Then MsgBox "Don't Do That" Exit Sub End If -- Jim Cone Portland, Oregon USA http:...

Report filter question
Hi, I've done filters many times before, but something is not right and I can not see it. My report is running of a query which has NO selection criteria in it. It is just a logical view of a table. I have the following code in my report (it is hard coded only because I'm trying to debug the code": Me.Filter = "(((tblMailingList.MemType)='MT-S'))" Me.FilterOn = True I know that the filter code is correct because I went into design query and put MT-S for the Criteria in the field MemType and view the resulting SQL cod...

Macro Button Question
Heya All, I am trying to insert and delete rows using macros. Ive assigned the Macro to a button, but would like to be able to insert (and delete) exactly where the button is on the worksheet, no matter where the activecell is.. So, I guess my question is, is it possible to get the cell reference behind a graphic button? Or, even better, assign a macro to a cell? ta, Maria Maria There are "insert row" and "insert column" buttons available from Tools>Customize>Commands>Insert Also "delete row" and "delete column" buttons available from To...

Question on Mutex
Hello, The following code is being invoked when multiple files are selected from the contextmenu. The first selected file enters into the "WAIT_OBJECT_0" case and the messagebox properly displays the filename. The second file enters into the "WAIT_ABANDONED" case. The remaining files enters into the WAIT_OBJECT_0 case properly. Does anyone know why the mutex handle gets abandoned ONLY after processing the first file...the mutex handle was released properly without any errors. The scenario can ge repeated many times with the consistant results. Thank You! #include &...

Chart resize macro
I have a sheet that contains about 32 charts, since there are so man charts I reduced the scale of the sheet so I can see all charts Problem is the charts are so small I can't interpret them. I want t create a macro that re-sizes them when I click on them without havin to write a separate macro for each chart. Is there a way to return th name of a chart to a variable? Here is what i have so far: Dim ChtOb As ChartObject With ActiveSheet Set ChtOb = .ChartObjects("chart 1") If ChtOb.Height < 1500 Then With ChtOb .Height = 1500 .Width = 2800 .Top = 34 .Left = 43 End With ...