Excel Com Addin

I've gotten the Com Addin to work, my problem is this. I need to allow
the user to select a range of cells (All in the same column) and then
select my addin. My addin needs to pick up this range of cells and
then parse the value of each cell in this range. I have looked, and
though I had it, but nadda.
I am using VB.NET to create the addin.

Thanks,
Jody W

Try using an InputBox() function with Type:= 8

Nick Hebb wrote:

> Try using an InputBox() function with Type:= 8

InputBox?? Make the user type the cellrange in? I was kinda hoping to
allow the user to use the mouse, select his/her cells, and detect
these...

I think you should try it...
It should create a RefEdit box that does what you want.

--
steveB

Remove "AYN" from email to respond
"Jody L. Whitlock" <tierscheiss1977@hotmail.com> wrote in message
news:ekCiZrTbFHA.348@TK2MSFTNGP14.phx.gbl...
> Nick Hebb wrote:
>
>> Try using an InputBox() function with Type:= 8
>
> InputBox?? Make the user type the cellrange in? I was kinda hoping to
> allow the user to use the mouse, select his/her cells, and detect
> these...

STEVE BELL wrote:

> I think you should try it...
> It should create a RefEdit box that does what you want.

RefEditBox???? Now I'm confused, I thought we were talking InputBoxes?
No, just kidding, sory. Anyhew, I will discuss with the team if
InputBox is acceptable....

Thanks,
Jody W.

There's a difference between: Inputbox and application.inputbox.


"Jody L. Whitlock" wrote:
>
> STEVE BELL wrote:
>
> > I think you should try it...
> > It should create a RefEdit box that does what you want.
>
> RefEditBox???? Now I'm confused, I thought we were talking InputBoxes?
> No, just kidding, sory. Anyhew, I will discuss with the team if
> InputBox is acceptable....
>
> Thanks,
> Jody W.

--

Dave Peterson

Type:= 8 means user can select a range, just as you want.

RBS


"Jody L. Whitlock" <tierscheiss1977@hotmail.com> wrote in message
news:ekCiZrTbFHA.348@TK2MSFTNGP14.phx.gbl...
> Nick Hebb wrote:
>
>> Try using an InputBox() function with Type:= 8
>
> InputBox?? Make the user type the cellrange in? I was kinda hoping to
> allow the user to use the mouse, select his/her cells, and detect
> these...

Excel has its own InputBox, separate from the standard VBA
InputBox. When you set the Type parameter to 8, the use can
select a range using the mouse. E.g.,

Dim Rng As Range
On Error Resume Next
Set Rng = Application.InputBox("Select A Range", Type:=8)
If Not Rng Is Nothing Then
MsgBox "You selected: " & Rng.Address
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Jody L. Whitlock" <tierscheiss1977@hotmail.com> wrote in message
news:ekCiZrTbFHA.348@TK2MSFTNGP14.phx.gbl...
> Nick Hebb wrote:
>
>> Try using an InputBox() function with Type:= 8
>
> InputBox?? Make the user type the cellrange in? I was kinda
> hoping to
> allow the user to use the mouse, select his/her cells, and
> detect
> these...

Chip Pearson wrote:

> Excel has its own InputBox, separate from the standard VBA
> InputBox. When you set the Type parameter to 8, the use can
> select a range using the mouse. E.g.,
>
> Dim Rng As Range
> On Error Resume Next
> Set Rng = Application.InputBox("Select A Range", Type:=8)
> If Not Rng Is Nothing Then
> MsgBox "You selected: " & Rng.Address
> End If

That worked very nicely, thank you! Now, I just need to figure out how
to loop through every cell in that range, tally up the results, then
write the tally into another cell. Is there a good site that explains
alot of this stuff?

Thanks,
jody

Dim Rng As Range
Dim cell As Range
Dim tmp
On Error Resume Next
Set Rng = Application.InputBox("Select A Range", Type:=8)
If Not Rng Is Nothing Then
MsgBox "You selected: " & Rng.Address
End If

For Each cell In Rng
If IsNumeric(cell.Value) Then
tmp = tmp + cell.Value
End If
Next cell

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jody L. Whitlock" <tierscheiss1977@hotmail.com> wrote in message
news:uaeJ$BfcFHA.2440@TK2MSFTNGP10.phx.gbl...
> Chip Pearson wrote:
>
> > Excel has its own InputBox, separate from the standard VBA
> > InputBox. When you set the Type parameter to 8, the use can
> > select a range using the mouse. E.g.,
> >
> > Dim Rng As Range
> > On Error Resume Next
> > Set Rng = Application.InputBox("Select A Range", Type:=8)
> > If Not Rng Is Nothing Then
> > MsgBox "You selected: " & Rng.Address
> > End If
>
> That worked very nicely, thank you! Now, I just need to figure out how
> to loop through every cell in that range, tally up the results, then
> write the tally into another cell. Is there a good site that explains
> alot of this stuff?
>
> Thanks,
> jody

If you have access to the XL application in your add-in (say its called
xlApp), you could always use xlApp.Selection. Verify its type is Range
and then do whatever it is you want to do with it.

Personally, when I start a new add-in, I often rely on things like the
selection. But before I unleash it on an unsuspecting world, I always
replace the interface with a userform (windows form in .Net?)


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <elRRt6SbFHA.2996@TK2MSFTNGP10.phx.gbl>, tierscheiss1977
@hotmail.com says...
> I've gotten the Com Addin to work, my problem is this. I need to allow
> the user to select a range of cells (All in the same column) and then
> select my addin. My addin needs to pick up this range of cells and
> then parse the value of each cell in this range. I have looked, and
> though I had it, but nadda.
> I am using VB.NET to create the addin.
>
> Thanks,
> Jody W
>

Sorry, I could have been a little more detailed. here is a sample that
shows what I'm talking about:

Sub tryIt()
Dim res As Range
Dim def As Range

Set def = Application.Selection

Set res = Application.InputBox("Select the range of cells",
Type:=8, _
Default:=def.AddressLocal)

MsgBox res.Address

End Sub

The Type:=8 tells it to let the user select a range. The
Default:=def.AddressLocal gets the range the user already has selected.
This way, if the user already has a range selected, they only have to
click OK, but if they initiated the action and didn't realize they
needed to pre-select the range, the InputBox allows to select it.

Also, to make sure your application is robust, you should verify the
type of the Selection before assigning it to the def range object. The
user's current selection could be a chart or an autoshape, either of
which would cause the above sample to throw an error.

Yes, I know what you were referring to. But note that the OP is
writing a .Net DLL that will be used as a COM Add-In. Consequently,
one doesn't have access to 'Application' but needs to establish one at
add-in initialization time through a variable s/he declares in the COM
Add-in.

Further, and you may already know the answer to this, but I don't, one
should check the interaction between XL, the COM Add-In, and the XL
InputBox dialog box. Will it still be modal? I know that if one has a
userform in a COM Add-In, one needs to take special steps to make it
modal and to ensure it shows within the XL window. Further, it is not
possible to use a RefEdit control in such a userform. Well, one can
create one but it doesn't work. Does InputBox with Type=8 have the
same problem?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <1118378498.397110.257700@o13g2000cwo.googlegroups.com>,
n.hebb@comcast.net says...
> Sorry, I could have been a little more detailed. here is a sample that
> shows what I'm talking about:
>
> Sub tryIt()
> Dim res As Range
> Dim def As Range
>
> Set def = Application.Selection
>
> Set res = Application.InputBox("Select the range of cells",
> Type:=8, _
> Default:=def.AddressLocal)
>
> MsgBox res.Address
>
> End Sub
>
> The Type:=8 tells it to let the user select a range. The
> Default:=def.AddressLocal gets the range the user already has selected.
> This way, if the user already has a range selected, they only have to
> click OK, but if they initiated the action and didn't realize they
> needed to pre-select the range, the InputBox allows to select it.
>
> Also, to make sure your application is robust, you should verify the
> type of the Selection before assigning it to the def range object. The
> user's current selection could be a chart or an autoshape, either of
> which would cause the above sample to throw an error.
>
>

Roedd <<Tushar Mehta>> wedi ysgrifennu:


> Further, and you may already know the answer to this, but I don't, one
> should check the interaction between XL, the COM Add-In, and the XL
> InputBox dialog box. Will it still be modal? I know that if one has
> a userform in a COM Add-In, one needs to take special steps to make it
> modal and to ensure it shows within the XL window. Further, it is not
> possible to use a RefEdit control in such a userform. Well, one can
> create one but it doesn't work. Does InputBox with Type=8 have the
> same problem?
>

FWIW, I once created an ActiveX control for forms in VB6 dlls that emulated
the RefEdit using a textbox, a commandbutton and Excel's Inputbox. It worked
superbly. I was going to bring it up to commercial standard, but I didn't
think the audience was there and then along came .NET

<unhelpful>I have not the slightest idea whether a similar .NET control
could be created.</unhelpful>

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.

I did something similar (refedit in a userform shown from a COM Add-in)
using a 2nd userform. Using XL's InputBox didn't occur to me. Goes to
show how often I call on it. {grin}

Anyway, the files describing the technique have been added to the other
gazillion files in my 'tutorials for the website' folder. {grin}

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <e3sJlVbbFHA.2756@tk2msftngp13.phx.gbl>, rob@analytical-
dynamicsdotcodotukay says...
> Roedd <<Tushar Mehta>> wedi ysgrifennu:
>
>
> > Further, and you may already know the answer to this, but I don't, one
> > should check the interaction between XL, the COM Add-In, and the XL
> > InputBox dialog box. Will it still be modal? I know that if one has
> > a userform in a COM Add-In, one needs to take special steps to make it
> > modal and to ensure it shows within the XL window. Further, it is not
> > possible to use a RefEdit control in such a userform. Well, one can
> > create one but it doesn't work. Does InputBox with Type=8 have the
> > same problem?
> >
>
> FWIW, I once created an ActiveX control for forms in VB6 dlls that emulated
> the RefEdit using a textbox, a commandbutton and Excel's Inputbox. It worked
> superbly. I was going to bring it up to commercial standard, but I didn't
> think the audience was there and then along came .NET
>
> <unhelpful>I have not the slightest idea whether a similar .NET control
> could be created.</unhelpful>
>
>