How to get users' range selection in VBA macro?
 

Dear experts,

How can i get user's input about range address in Macros? I know
inputbox can do that like
dim rng as range
set rng =inputbox Prompt:="Getting Range Address", Title:="Test",
Type:=8

But the interface is too simple, and it's not easy for common users
who're not experienced at Excel as they might think they have to write
the address and errors might happen.

I hope I can use some tools like boxes when using functions in GUI
interface, which are familiar to everyone.

or you have better solutions. Many thanks in advance...

How to get users' range selection in VBA macro?
 

The following is my code, and I want to replace this:

Set Labels(i) = Application.InputBox(prompt:="Select the Range Address
for Series " & i, _
Title:="Selection", Type:=8)

The Full code:

Sub AddDataLables()
Dim Labels() As Range
Dim dialogrange As Range
Dim LabelCell As Range
Dim LabelValue As String
Dim SeriesCounts As Integer
Dim DataLabelCounts As Integer
Dim i, j As Integer
j = 1
If ActiveSheet.ChartObjects.Count = 0 Then
MsgBox prompt:="No Charts Found in the sheet," _
& Chr(10) & "Please CHANGE sheets or" & Chr(10) & _
"Make a NEW chart", Buttons:=vbExclamation, Title:="Error Found"
Exit Sub
End If
SeriesCounts = ActiveSheet.ChartObjects(1).Chart. _
SeriesCollection.Count
ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1).HasDataLabels =
True
DataLabelCounts = ActiveSheet.ChartObjects(1).Chart. _
SeriesCollection(1).DataLabels.Count
ReDim Labels(1 To SeriesCounts)
For i = 1 To SeriesCounts
Set Labels(i) = Application.InputBox(prompt:="Select the Range
Address for Series " & i, _
Title:="Selection", Type:=8)
ActiveSheet.ChartObjects(1).Chart.SeriesCollection (i).HasDataLabels
= True
For Each LabelCell In Labels(i)
ActiveSheet.ChartObjects(1).Chart. _
SeriesCollection(i).DataLabels(j - (i - 1) *
DataLabelCounts).Text = LabelCell.Value
j = j + 1
Next LabelCell
Next i
End Sub

On 12ÔÂ1ÈÕ, ÏÂÎç6ʱ44·Ö, "Samuel Wu"
wrote:
Dear experts,

How can i get user's input about range address in Macros? I know
inputbox can do that like
dim rng as range
set rng =inputbox Prompt:="Getting Range Address", Title:="Test",
Type:=8

But the interface is too simple, and it's not easy for common users
who're not experienced at Excel as they might think they have to write
the address and errors might happen.

I hope I can use some tools like boxes when using functions in GUI
interface, which are familiar to everyone.

or you have better solutions. Many thanks in advance...

How to get users' range selection in VBA macro?
 

thanks, John.
I'm now doing like the code says, but I found lots of mistakes from
users. They have little knowledge of Excel. They suggest me adding that
function. I hope no special train to them about my addin because
they're my BOSES

On 12ÔÂ1ÈÕ, ÏÂÎç7ʱ35·Ö, John Bundy
wrote:
It never seems simple enough...

the most intuitive to me is the refedit control that can be placed on a
userform, users should understand what is wanted. Thats just my opinion
though.

-John

"Samuel Wu" wrote:
The following is my code, and I want to replace this:

Set Labels(i) = Application.InputBox(prompt:="Select the Range Address
for Series " & i, _
Title:="Selection", Type:=8)

The Full code:

Sub AddDataLables()
Dim Labels() As Range
Dim dialogrange As Range
Dim LabelCell As Range
Dim LabelValue As String
Dim SeriesCounts As Integer
Dim DataLabelCounts As Integer
Dim i, j As Integer
j = 1
If ActiveSheet.ChartObjects.Count = 0 Then
MsgBox prompt:="No Charts Found in the sheet," _
& Chr(10) & "Please CHANGE sheets or" & Chr(10) & _
"Make a NEW chart", Buttons:=vbExclamation, Title:="Error Found"
Exit Sub
End If
SeriesCounts = ActiveSheet.ChartObjects(1).Chart. _
SeriesCollection.Count
ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1).HasDataLabels =
True
DataLabelCounts = ActiveSheet.ChartObjects(1).Chart. _
SeriesCollection(1).DataLabels.Count
ReDim Labels(1 To SeriesCounts)
For i = 1 To SeriesCounts
Set Labels(i) = Application.InputBox(prompt:="Select the Range
Address for Series " & i, _
Title:="Selection", Type:=8)
ActiveSheet.ChartObjects(1).Chart.SeriesCollection (i).HasDataLabels
= True
For Each LabelCell In Labels(i)
ActiveSheet.ChartObjects(1).Chart. _
SeriesCollection(i).DataLabels(j - (i - 1) *
DataLabelCounts).Text = LabelCell.Value
j = j + 1
Next LabelCell
Next i
End Sub

On 12ÔÂ1ÈÕ, ÏÂÎç6ʱ44·Ö, "Samuel Wu"
wrote:
Dear experts,

How can i get user's input about range address in Macros? I know
inputbox can do that like
dim rng as range
set rng =inputbox Prompt:="Getting Range Address", Title:="Test",
Type:=8

But the interface is too simple, and it's not easy for common users
who're not experienced at Excel as they might think they have to write
the address and errors might happen.

I hope I can use some tools like boxes when using functions in GUI
interface, which are familiar to everyone.

or you have better solutions. Many thanks in advance...