Selection as String

**Shakespear** · 08-05-2005, 09:17 AM

Hi.

I have created a macro in Excel which have a Form. The form contains among other things a RefEdit. When I launch the macro this RefEdit is empty, but I want it to contain for example Sheet1!$F$2:$F$9 as the default value if these cells are selected in the active sheet.

So what I need to know is if there is a way to get the selected range/cell(s) as a string representation.

Pseudo code:
-----------------------------

Public Sub MyMacro()
  Dim MyRefString As String

  ' Start of getting Selection as String-->
    MyRefString = ...
  '<-- End of getting Selection as String

  With MyForm
    .MyRefEdit.Value = MyRefString
  End With
...
End Sub

-------------------------------

Any ideas?

/Jörgen

K Dales · 08-05-2005, 11:05 AM

ActiveSheet.Name & "!" & Selection.Address
--
- K Dales

"Shakespear" wrote:

>
> Hi.
>
> I have created a macro in Excel which have a Form. The form contains
> among other things a RefEdit. When I launch the macro this RefEdit is
> empty, but I want it to contain for example Sheet1!$F$2:$F$9 as the
> default value if these cells are selected in the active sheet.
>
> So what I need to know is if there is a way to get the selected
> range/cell(s) as a string representation.
>
> Pseudo code:
> -----------------------------
>
> Code:
> --------------------
>
> Public Sub MyMacro()
> Dim MyRefString As String
>
> ' Start of getting Selection as String-->
> MyRefString = ...
> '<-- End of getting Selection as String
>
> With MyForm
> .MyRefEdit.Value = MyRefString
> End With
> ...
> End Sub
>
> --------------------
>
> -------------------------------
>
> Any ideas?
>
> /JÃ¶rgen
>
>
> --
> Shakespear
> ------------------------------------------------------------------------
> Shakespear's Profile: http://www.excelforum.com/member.php...o&userid=25957
> View this thread: http://www.excelforum.com/showthread...hreadid=393283
>
>

Bernie Deitrick · 08-05-2005, 11:05 AM

Jörgen,

UserForm1.RefEdit1.Text = Range("F2:F9").Address(True, True, xlA1, True)

Using your object names:

MyRefString = Range("F2:F9").Address(True, True, xlA1, True)
MyForm.MyRefEdit.Text = MyRefString

HTH,
Bernie
MS Excel MVP

"Shakespear" <[email protected]> wrote in message
news:[email protected]...
>
> Hi.
>
> I have created a macro in Excel which have a Form. The form contains
> among other things a RefEdit. When I launch the macro this RefEdit is
> empty, but I want it to contain for example Sheet1!$F$2:$F$9 as the
> default value if these cells are selected in the active sheet.
>
> So what I need to know is if there is a way to get the selected
> range/cell(s) as a string representation.
>
> Pseudo code:
> -----------------------------
>
> Code:
> --------------------
>
> Public Sub MyMacro()
> Dim MyRefString As String
>
> ' Start of getting Selection as String-->
> MyRefString = ...
> '<-- End of getting Selection as String
>
> With MyForm
> .MyRefEdit.Value = MyRefString
> End With
> ...
> End Sub
>
> --------------------
>
> -------------------------------
>
> Any ideas?
>
> /Jörgen
>
>
> --
> Shakespear
> ------------------------------------------------------------------------
> Shakespear's Profile: http://www.excelforum.com/member.php...o&userid=25957
> View this thread: http://www.excelforum.com/showthread...hreadid=393283
>

Bernie Deitrick · 08-05-2005, 11:05 AM

Sorry, missed the part about using the current selection:

MyRefString = Selection.Address(True, True, xlA1, True)
MyForm.MyRefEdit.Text = MyRefString

HTH,
Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Jörgen,
>
> UserForm1.RefEdit1.Text = Range("F2:F9").Address(True, True, xlA1, True)
>
> Using your object names:
>
> MyRefString = Range("F2:F9").Address(True, True, xlA1, True)
> MyForm.MyRefEdit.Text = MyRefString
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Shakespear" <[email protected]> wrote in message
> news:[email protected]...
>>
>> Hi.
>>
>> I have created a macro in Excel which have a Form. The form contains
>> among other things a RefEdit. When I launch the macro this RefEdit is
>> empty, but I want it to contain for example Sheet1!$F$2:$F$9 as the
>> default value if these cells are selected in the active sheet.
>>
>> So what I need to know is if there is a way to get the selected
>> range/cell(s) as a string representation.
>>
>> Pseudo code:
>> -----------------------------
>>
>> Code:
>> --------------------
>>
>> Public Sub MyMacro()
>> Dim MyRefString As String
>>
>> ' Start of getting Selection as String-->
>> MyRefString = ...
>> '<-- End of getting Selection as String
>>
>> With MyForm
>> .MyRefEdit.Value = MyRefString
>> End With
>> ...
>> End Sub
>>
>> --------------------
>>
>> -------------------------------
>>
>> Any ideas?
>>
>> /Jörgen
>>
>>
>> --
>> Shakespear
>> ------------------------------------------------------------------------
>> Shakespear's Profile: http://www.excelforum.com/member.php...o&userid=25957
>> View this thread: http://www.excelforum.com/showthread...hreadid=393283
>>
>
>