Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA, Range Variable into String Variable. Diaplay elements of Range

Posted on 2011-04-27
7
Medium Priority
?
242 Views
Last Modified: 2012-05-11

Hi guys

I would like to choose a Range from Excel worksheet and then be able to msgbox the result in a string like this:
'[Workbook.xls]Sheet1'!$A$1:$A$4

To include the name of the workbook file as well.

The Code goes like below but I need to modify it because I get errors.

Sub RangeToString()

    Dim myRange As Range
    
       Set myRange = Application.InputBox("LIST 1:" & vbCrLf & "Select Range", Type:=8)
    
    MsgBox myRange

End Sub

Open in new window




0
Comment
Question by:New_Alex
  • 4
  • 3
7 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35473309
Use this code:

Sub RangeToString()

    Dim myRange As Range
   
       Set myRange = Application.InputBox("LIST 1:" & vbCrLf & "Select Range", Type:=8)
   
    MsgBox Join(Application.Transpose(Application.Transpose(myRange.Value)), ", ")

End Sub

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35473314
Handles row and columns:

Sub RangeToString()

    Dim myRange As Range
   
       Set myRange = Application.InputBox("LIST 1:" & vbCrLf & "Select Range", Type:=8)
   
    If myRange.Rows.Count = 1 Then
        MsgBox Join(Application.Transpose(Application.Transpose(myRange.Value)), ", ")
    ElseIf myRange.Columns.Count = 1 Then
        MsgBox Join(Application.Transpose(myRange.Value), ", ")
    Else
        MsgBox "Select a single row or column of cells."
    End If

End Sub

Kevin
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 35473320
Oops, misunderstood the question:

Sub RangeToString()

    Dim myRange As Range
   
       Set myRange = Application.InputBox("LIST 1:" & vbCrLf & "Select Range", Type:=8)
   
    MsgBox myRange.Address(External:=True)

End Sub

Kevin
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 1

Author Comment

by:New_Alex
ID: 35473339
Thanks Zorvek. But when I say the "Result" I mean the result of the choice within the Input Box which is something like:

'[Workbook.xls]Sheet1'!$A$1:$A$4

Open in new window

0
 
LVL 1

Author Closing Comment

by:New_Alex
ID: 35473347
YEAH WONDERFUL

Thank you matey.... Wonderful !!!

Works like a charm !!!
 

THANKSZZZZZzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35473349
Yep. That's what my third post does. The first two were a misunderstanding.

Kevin
0
 
LVL 1

Author Comment

by:New_Alex
ID: 35473367
Yeaahhhh Man Exactly What I needed. I was at work and stack on that issue until you came.

!!!!!!!!!!!!!!!
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question