How do you use the InputBox to set ranges for charts?
For example, to create a chart, I use:
Sub CreateChart()
Dim objChart As ChartObject
Dim myDataRange As Range
Dim myChtRange As Range
With ActiveSheet
Set myDataRange = Range("B5:E6")
Set myChtRange = Range("B11:E22")
Set objChart = .ChartObjects.Add( _
Left:=myChtRange.Left, Top:=myChtRange.Top, _
Width:=myChtRange.Width, Height:=myChtRange.Height)
With objChart.Chart
.ChartArea.AutoScaleFont = False
.ChartType = xlPieExploded
.SetSourceData Source:=myDataRange
.HasLegend = False
.HasTitle = False
End With
End With
End Sub
This code lets you select a range of cells to highlight yellow:
https://www.thespreadsheetguru.com/s...with-inputbox/
Sub HighlightCells()
Dim rng As Range
Dim DefaultRange As Range
Dim FormatRuleInput As String
If TypeName(Selection) = "Range" Then
Set DefaultRange = Selection
Else
Set DefaultRange = ActiveCell
End If
On Error Resume Next
Set rng = Application.InputBox( _
Title:="Highlight Cells Yellow", _
Prompt:="Select a cell range to highlight yellow", _
Default:=DefaultRange.Address, _
Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
rng.Interior.color = vbYellow
End Sub
And this code lets you select the chart source data for the active chart:
https://peltiertech.com/assign-chart...ries-with-vba/
Sub SelectChartSourceDataRange()
If ActiveChart Is Nothing Then
MsgBox "Select a chart and try again.", vbExclamation, "No Chart Selected"
Else
Dim Prompt As String
Prompt = "Select a range that contains source data for the active chart."
Dim Title As String
Title = "Select Chart Source Data Range"
Dim ChartSourceData As Range
On Error Resume Next
Set ChartSourceData = Application.InputBox(Prompt, Title, , , , , , 8)
On Error GoTo 0
If Not ChartSourceData Is Nothing Then
' if a range was selected
If ChartSourceData.Rows.Count >= ChartSourceData.Columns.Count Then
Dim DataOrientation As XlRowCol
DataOrientation = xlColumns
Else
DataOrientation = xlRows
End If
ActiveChart.SetSourceData ChartSourceData, DataOrientation
End If
End If
End Sub
How would you build the create chart macro with the InputBox? So that you can select:
myDataRange = Range("B5:E6")
myChtRange = Range("B11:E22")
Thank you very much!
Bookmarks