Results 1 to 14 of 14

VBA How to use the InputBox to create charts

Threaded View

  1. #1
    Registered User
    Join Date
    03-10-2024
    Location
    Berlin
    MS-Off Ver
    mac2016
    Posts
    94

    VBA How to use the InputBox to create charts

    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!
    Last edited by briskie; 04-17-2024 at 10:29 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. quick question regarding setting ranges mid-routine
    By terriertrip in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2020, 01:47 PM
  2. Modify routine to add / delete sheets with names from input ranges
    By cvmelkus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2015, 03:31 PM
  3. [SOLVED] Inputbox defining the Pathname is only opening the first workbook and not running routine
    By graym463 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-19-2015, 08:41 AM
  4. How to modify routine to add / delete / rename tabs from input ranges
    By cvmelkus in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-17-2015, 03:08 PM
  5. [SOLVED] Passing Multiple ranges to Sub Routine
    By madhatter40 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-20-2013, 04:19 PM
  6. Replies: 2
    Last Post: 09-15-2009, 10:33 AM
  7. VBA code for InputBox vbCancel to run the routine
    By Glenn P in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-21-2005, 06:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1