Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #107941 > unrolled thread

User Selection as to which Function to Run

Back to article view | Back to microsoft.public.excel.programming

Contents

  User Selection as to which Function to Run "JCO" <someone@somewhere.com> - 2015-08-07 13:39 -0500
    Re: User Selection as to which Function to Run Claus Busch <claus_busch@t-online.de> - 2015-08-07 21:08 +0200
      Re: User Selection as to which Function to Run "JCO" <someone@somewhere.com> - 2015-08-08 16:26 -0500
    Re: User Selection as to which Function to Run Claus Busch <claus_busch@t-online.de> - 2015-08-09 19:07 +0200
      Re: User Selection as to which Function to Run "JCO" <someone@somewhere.com> - 2015-08-13 14:31 -0500
        Re: User Selection as to which Function to Run GS <gs@v.invalid> - 2015-08-13 20:23 -0400
          Re: User Selection as to which Function to Run "JCO" <someone@somewhere.com> - 2015-08-18 18:05 -0500
            Re: User Selection as to which Function to Run GS <gs@v.invalid> - 2015-08-18 19:54 -0400

#107941 — 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

[toc] | [next] | [standalone]

#107942

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

[toc] | [prev] | [next] | [standalone]

#107944

"Claus Busch"  wrote in message news:mq2vkm$jd4$1@dont-email.me...

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.

[toc] | [prev] | [next] | [standalone]

#107958

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 here:
https://onedrive.live.com/redir?resid=9378AAB6121822A3%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

[toc] | [prev] | [next] | [standalone]

#107961

"Claus Busch"  wrote in message news:mq818i$1ck$1@dont-email.me...

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 here:
https://onedrive.live.com/redir?resid=9378AAB6121822A3%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 

[toc] | [prev] | [next] | [standalone]

#107962

"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

[toc] | [prev] | [next] | [standalone]

#107980

"GS"  wrote in message news:mqjcbk$1t8$1@dont-email.me...

"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

[toc] | [prev] | [next] | [standalone]

#107981

> 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

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web