User Selection as to which Function to Run
 

I'm using Excel from Office 2013:

I want to query the user as to which function of 3-choices to run. I'm
currently set up to select a range of data using the mouse, then run the
proper 1 of 3 functions against the selection.

I suspect I get the selection up front, maybe dropdown list if that is
possible, then that code executes on the selection. That would be nice if
possible.

Thanks

User Selection as to which Function to Run
 

Hi,

Am Fri, 7 Aug 2015 13:39:13 -0500 schrieb JCO:

I suspect I get the selection up front, maybe dropdown list if that is
possible, then that code executes on the selection. That would be nice if
possible.

your numbers in column A, the validation list in B1.
First select the expected function

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) < "B1" Then Exit Sub
Dim myRng As Range

On Error Resume Next
Set myRng = Application.InputBox("Select your range", "Selection",
Type:=8)
If myRng Is Nothing Then Exit Sub

Range("C1") = "=" & Target & "(" & myRng.Address & ")"
End Sub

The result is written to C1


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

User Selection as to which Function to Run
 

"Claus Busch" wrote in message ...

Hi,

Am Fri, 7 Aug 2015 13:39:13 -0500 schrieb JCO:

I suspect I get the selection up front, maybe dropdown list if that is
possible, then that code executes on the selection. That would be nice if
possible.

your numbers in column A, the validation list in B1.
First select the expected function

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) < "B1" Then Exit Sub
Dim myRng As Range

On Error Resume Next
Set myRng = Application.InputBox("Select your range", "Selection",
Type:=8)
If myRng Is Nothing Then Exit Sub

Range("C1") = "=" & Target & "(" & myRng.Address & ")"
End Sub

The result is written to C1


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks Claus
Is there anyway to not define the control on the sheet. I was just thinking
to create the dropdown as a popup type then after capturing the input,
destroy the dropdown.

User Selection as to which Function to Run
 

Hi,

Am Fri, 7 Aug 2015 13:39:13 -0500 schrieb JCO:

I want to query the user as to which function of 3-choices to run. I'm
currently set up to select a range of data using the mouse, then run the
proper 1 of 3 functions against the selection.

please look he
https://onedrive.live.com/redir?resi...21822A3%214224
for "Function" and download the file because macros are disabled in
OneDrive.
Select a range with more than 1 cell and then select the function.


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

User Selection as to which Function to Run
 

"Claus Busch" wrote in message ...

Hi,

Am Fri, 7 Aug 2015 13:39:13 -0500 schrieb JCO:

I want to query the user as to which function of 3-choices to run. I'm
currently set up to select a range of data using the mouse, then run the
proper 1 of 3 functions against the selection.

please look he
https://onedrive.live.com/redir?resi...21822A3%214224
for "Function" and download the file because macros are disabled in
OneDrive.
Select a range with more than 1 cell and then select the function.


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

Thanks
This makes sense, but you had to use VB instead of Excel Macros (it
appears...?). That is okay, I'm not sure what the advantages/disadvantages
of using VB will have. I'm a bit confused with Excel because it has its own
set of Formulas, Macros & is native to the VB Language. I never know the
ramifications of using one over the other....lol

User Selection as to which Function to Run
 

"Thanks
This makes sense, but you had to use VB instead of Excel Macros (it
appears...?). That is okay, I'm not sure what the
advantages/disadvantages of using VB will have. I'm a bit confused
with Excel because it has its own set of Formulas, Macros & is native
to the VB Language. I never know the ramifications of using one over
the other.."

VBA (Visual Basic for Applications) is the macro language used with MS
Office and many other apps that use it for macros. Excel macros can
also be used as user-defined functions so they work in/as worksheet
formulas.

--
Garry

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

User Selection as to which Function to Run
 

"GS" wrote in message ...

"Thanks
This makes sense, but you had to use VB instead of Excel Macros (it
appears...?). That is okay, I'm not sure what the
advantages/disadvantages of using VB will have. I'm a bit confused
with Excel because it has its own set of Formulas, Macros & is native
to the VB Language. I never know the ramifications of using one over
the other.."

VBA (Visual Basic for Applications) is the macro language used with MS
Office and many other apps that use it for macros. Excel macros can
also be used as user-defined functions so they work in/as worksheet
formulas.

--
Garry

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

Thanks Garry,
Yeah I know VB is native to Excel. I guess I didn't make myself very clear.
I currently have a .bas file which is a general purpose utility that I
attach to different files just to help do the mundane tasks. I have 3-minor
routines that I wanted to combine a one routine (Macro). So it starts out
by determining the selection from the mouse, determines 1 of 3 Macros, then
executes it. So my real question was having the issue that my files are in
a .bas yet the dropdown list is in VB which will determine which of 3-macros
(in the .bas) file to execute. That's what I meant by advantages and
disadvantages of VB vs Macro routines.
JCO

User Selection as to which Function to Run
 

Thanks Garry,
Yeah I know VB is native to Excel. I guess I didn't make myself very
clear. I currently have a .bas file which is a general purpose
utility that I attach to different files just to help do the mundane
tasks. I have 3-minor routines that I wanted to combine a one
routine (Macro). So it starts out by determining the selection from
the mouse, determines 1 of 3 Macros, then executes it. So my real
question was having the issue that my files are in a .bas yet the
dropdown list is in VB which will determine which of 3-macros (in the
.bas) file to execute. That's what I meant by advantages and
disadvantages of VB vs Macro routines.

Well there is no difference!!

Perhaps simply...

Sub Run3Macros()
Call Macro1
Call Macro2
Call Macro3
End Sub

--
Garry

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