Re: Proper Way To Use InputBox In Absence of RefEdit?

[Govert van Drimmelen]

Hi Faraz,

Before I try to reproduce your problem, could you perhaps explain how
the .NET InputBox is "not working out perfectly"?
* Is the InputBox displayed?
* Is the result of the InputBox a Range object?

Some related links that you might be useful, where the RefEdit control
has been re-implemented in .NET:
* http://www.codeproject.com/Articles/32805/RefEdit-Emulation-for-NET
* http://www.codeproject.com/Articles/34425/VS-NET-Excel-Addin-Refedit-Control
* http://www.breezetree.com/blog/index.php/excel-refedit-in-c-sharp/

If you try any of these, please let us know what you find. I've not
had a closer look myself.

Regards,
Govert


* * * * * * * * * * * * * * * * * * * * * * * * * * * *
Ensure that the Excel-DNA project continues by
making your donation - http://excel-dna.net/support/
* * * * * * * * * * * * * * * * * * * * * * * * * * * *


On Apr 27, 8:03 pm, Faraz Ahmed Qureshi <farazem...@gmail.com> wrote:
> Hi Govert,
>
> Sure have found the experiments going on excellent and making me a fan of
> an ideal programmer like you and fellows on this great forum buddy.
> However, not finding any good or proper source in respect of selecting
> ranges for a TextBox on form in absence of an integrated control of
> RefEdit. In Excel I can use the native imputbox as in the following code to
> represent the selection address:
>
> Sub SelectionTest()
>   Dim SelectedAddress As Range
>   On Error Resume Next
>   Set SelectedAddress = Application.InputBox("Select your desired cells.",
> , , , , , , 8)
>   If SelectedAddress Is Nothing Then
>     MsgBox "No valid range was selected!"
>   Else
>     MsgBox "You selected " & SelectedAddress.Cells.Count & " cell(s)."
>   End If
> End Sub
>
> However, now in Visual Studio, what would be the proper way to return the
> similar address/reference to cells. Consider the following example yet
> worked out on back of a form to return the selection's address to a TextBox
> named *ListAddress* by pressing a button named *Button3*, but not working
> out perfectly:
>
>   Private Sub Button3_Click(sender As System.Object, e As System.EventArgs)
> Handles Button3.Click
>     ListAddress.Text = SelectionTest()
>   End Sub
>
>   Function SelectionTest()
>     Dim SelectedAddress As Object
>     On Error Resume Next
>     SelectedAddress = ExcelDnaUtil.Application.InputBox("Select your
> desired cells.", , , , , , , 8)
>     If SelectedAddress Is Nothing Then
>       SelectionTest = ""
>     Else
>       SelectionTest = "'" & SelectedAddress.Parent & "'!" &
> SelectedAddress.Address
>     End If
>   End Function
>
> What would be your valuable suggestion in this regard?
>
> Thanx again,
>
> Your ever-admiring fan,
>
> Faraz A Qureshi

[Govert van Drimmelen]

Hi Faraz,

You might try this code:

Function SelectionTest()
Dim SelectedAddress As Object

SelectedAddress = ExcelDnaUtil.Application.InputBox("Select
your desired cells.", , , , , , , 8)

If SelectedAddress = False Then
SelectionTest = ""
Else
SelectionTest = "'" & SelectedAddress.Parent.Name & "'!" &

SelectedAddress.Address
End If
End Function

Note the following:
1. The Excel InputBox returns "False" if you press Cancel.
2. The Excel InputBox does not allow you to put in an incorrect entry
and press OK. It shows the message:
"The reference you typed is not valid...."
3. VB.NET does not support default properties. So to get the name of
the selected range's parent sheet, you have to explicitly call
SelectedAddress.Parent.Name. You'll get an error if you try to use
SelectedAddress.Parent as a string.

-Govert

* * * * * * * * * * * * * * * * * * * * * * * * * * * *
Ensure that the Excel-DNA project continues by
making your donation - http://excel-dna.net/support/
* * * * * * * * * * * * * * * * * * * * * * * * * * * *

On Apr 29, 6:07 am, Faraz Ahmed Qureshi <farazem...@gmail.com> wrote:
> Thanx for your reply Govert.
>
> Actually, although I have been successful in using *InputBox* to quench the
> address of the *selected *cells, unfortunately if someone doesn't enter a
> proper entry in the same, how to determine the said fact?
>
> Consider the example where, if any irrelevant or incorrect entry is
> inserted in the inputbox, the VBA code i presented earlier does reflect the
> message *"No valid range was selected!"* because the *SelectedAddress Is
> Nothing*.
>
> How to determine the same in case of *.net *+* .dna* being used to
> determine if an object is not a *Valid Range* but some incorrect entry like
> *$A$1+$A10* instead of *$A$1:$A$10*, or *1A *instead of *A1*?
>
> With no earlier experience of VS or .net ever don't know the appropriate
> way of how to have the RefEdit desigened controls at sourcecode.com be
> added to my own .xll project.
>
> It's only and only your .dna that has convinced me to get, install and use
> the Visual Studio 2010.
>
> Thanx again for all your help buddy.

>
>
>
>
>
>
>
> On Sunday, 28 April 2013 14:26:09 UTC+5, Govert van Drimmelen wrote:
>
> > Hi Faraz,
>
> > Before I try to reproduce your problem, could you perhaps explain how
> > the .NET InputBox is "not working out perfectly"?
> > * Is the InputBox displayed?
> > * Is the result of the InputBox a Range object?
>
> > Some related links that you might be useful, where the RefEdit control
> > has been re-implemented in .NET:
> > *http://www.codeproject.com/Articles/32805/RefEdit-Emulation-for-NET
> > *

> >http://www.codeproject.com/Articles/34425/VS-NET-Excel-Addin-Refedit-...
> > *http://www.breezetree.com/blog/index.php/excel-refedit-in-c-sharp/

>
> > If you try any of these, please let us know what you find. I've not
> > had a closer look myself.
>
> > Regards,
> > Govert
>
> > * * * * * * * * * * * * * * * * * * * * * * * * * * * *
> >  Ensure that the Excel-DNA project continues by

> >  making your donation -http://excel-dna.net/support/

[Govert van Drimmelen]

Sorry - that won't work. You need to check the return type:

SelectedAddress = ExcelDnaUtil.Application.InputBox("Select
your desired cells.", , , , , , , 8)

>>>> If TypeOf SelectedAddress Is Boolean Then

SelectionTest = ""
Else
SelectionTest = "'" & SelectedAddress.Parent.Name & "'!" &
SelectedAddress.Address
End If

-Govert