Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 30th 13, 06:48 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 536
Default Run or Call a sub from the list of subs in a drop down.

If I have a list of ten sub names in a drop down F1, can I make my MySubCaller run the sub I choose in F1?

This little diddy doesn't work and the scant info I can find to do this leads me to think it is a bogus way to manage the running of ten subs from a single on sheet button.

Thanks,
Howard

Option Explicit

Sub MySubCaller()
Dim i As String
i = Range("F1").Value
Application.Run i
End Sub

  #2   Report Post  
Old September 30th 13, 07:14 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 536
Default Run or Call a sub from the list of subs in a drop down.

On Sunday, September 29, 2013 10:48:37 PM UTC-7, Howard wrote:
If I have a list of ten sub names in a drop down F1, can I make my MySubCaller run the sub I choose in F1?



This little diddy doesn't work and the scant info I can find to do this leads me to think it is a bogus way to manage the running of ten subs from a single on sheet button.



Thanks,

Howard



Option Explicit



Sub MySubCaller()

Dim i As String

i = Range("F1").Value

Application.Run i

End Sub


I found this but I still get an error 400 with:

Application.Run (i)


expression .Run(Macro,[Arg1, , , Arg30])
expression A variable that represents an Application object

Howard
  #3   Report Post  
Old September 30th 13, 08:12 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,712
Default Run or Call a sub from the list of subs in a drop down.

Hi Howard,

Am Sun, 29 Sep 2013 23:14:45 -0700 (PDT) schrieb Howard:

Application.Run (i)


try worksheet_change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$F$1" Then Exit Sub

Application.Run (Target)
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #4   Report Post  
Old September 30th 13, 09:11 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 536
Default Run or Call a sub from the list of subs in a drop down.

On Monday, September 30, 2013 12:12:49 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Sun, 29 Sep 2013 23:14:45 -0700 (PDT) schrieb Howard:



Application.Run (i)




try worksheet_change event:



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address < "$F$1" Then Exit Sub



Application.Run (Target)

End Sub





Regards

Claus B.


With this in the sheet 1 module I get an error on the App.Run(Target) line
saying macros may not be available or may be disabled.
(There are three more macros like the one shown)

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$F$1" Then Exit Sub
Application.Run (Target)
End Sub

Sub xx1()
MsgBox "one"
End Sub
  #5   Report Post  
Old September 30th 13, 09:18 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,712
Default Run or Call a sub from the list of subs in a drop down.

Hi Howard,

Am Mon, 30 Sep 2013 01:11:37 -0700 (PDT) schrieb Howard:

With this in the sheet 1 module I get an error on the App.Run(Target) line
saying macros may not be available or may be disabled.
(There are three more macros like the one shown)


please have a look:
https://skydrive.live.com/#cid=9378A...121822A3%21326
for the workbook "Macro". Download it with rightclick


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2


  #6   Report Post  
Old September 30th 13, 09:35 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,712
Default Run or Call a sub from the list of subs in a drop down.

Hi Howard,

Am Mon, 30 Sep 2013 01:11:37 -0700 (PDT) schrieb Howard:

Sub xx1()


xx1 is a cell reference. You can't name a macro like a cell reference


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #7   Report Post  
Old September 30th 13, 10:06 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,712
Default Run or Call a sub from the list of subs in a drop down.

Hi Howard,

Am Mon, 30 Sep 2013 10:35:40 +0200 schrieb Claus Busch:

xx1 is a cell reference. You can't name a macro like a cell reference


a macro named like a cell reference you can run from VBA editor.
If you try to run it from a sheet you can't, because the Run button is
disabled.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #8   Report Post  
Old September 30th 13, 11:12 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 536
Default Run or Call a sub from the list of subs in a drop down.

On Monday, September 30, 2013 2:06:43 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Mon, 30 Sep 2013 10:35:40 +0200 schrieb Claus Busch:



xx1 is a cell reference. You can't name a macro like a cell reference




a macro named like a cell reference you can run from VBA editor.

If you try to run it from a sheet you can't, because the Run button is

disabled.





Regards

Claus B.


Boy do I feel stupid. In my haste I just wanted to create four or five macros to set up my code. Completely over looked the cell ref's I was using.

Thanks Claus.

Regards,
Howard
Howard
  #9   Report Post  
Old September 30th 13, 04:09 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2011
Posts: 3,514
Default Run or Call a sub from the list of subs in a drop down.

On Monday, September 30, 2013 2:06:43 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Mon, 30 Sep 2013 10:35:40 +0200 schrieb Claus Busch:



xx1 is a cell reference. You can't name a macro like a cell
reference




a macro named like a cell reference you can run from VBA editor.

If you try to run it from a sheet you can't, because the Run button
is

disabled.





Regards

Claus B.


Boy do I feel stupid. In my haste I just wanted to create four or
five macros to set up my code. Completely over looked the cell ref's
I was using.

Thanks Claus.

Regards,
Howard
Howard


Test1()
Test2()
Test3()
Test4()
Test5()

OR as does the macro recorder...

Macro1()
Macro2()
Macro3()
Macro4()
Macro5()

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I call VBA subs from different spreadsheet from code behind form? clhamilton Excel Discussion (Misc queries) 1 June 12th 09 05:47 PM
how to I make member list that indicates when subs due Lozza Excel Worksheet Functions 2 January 17th 07 02:52 PM
multiple select from the drop down list in excel. list in one sheet and drop down in sriramus Excel Discussion (Misc queries) 5 October 27th 05 06:55 PM
Call subs from another wkbk? Huyeote[_3_] Excel Programming 4 September 1st 04 01:39 PM
Call VBA-Subs From Old XLM-Macros hglamy[_3_] Excel Programming 2 February 28th 04 08:43 PM


All times are GMT +1. The time now is 10:48 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017