Help adding input box to macro

[ttbuson]

Hi All,

I'd like to add a message box to the following macro.

Sub FormatDataPoints()
 
Dim i As Long

    With ActiveSheet.ChartObjects("Chart 1").Chart
        For i = 1 To 10
            With .SeriesCollection(i)
                .MarkerStyle = 2
                .MarkerSize = 7
                .Format.Fill.ForeColor.RGB = RGB(0, 128, 0)
                .Format.Line.Visible = msoFalse
                .ApplyDataLabels
                    With .DataLabels
                        .ShowSeriesName = True
                        .ShowValue = False
                        .Position = Above
                        .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 128, 0)
                    End With
            End With
        Next i
    End With
 
End Sub

Basically, I'd like to have a message box come up that asks the user to define which series the formatting should be applied to. So, the line in the macro where it says For i = x to y would be defined by the message box.

I'd also like to have a message box that would allow a user to select what color they wanted to use for the formatting, with the names matched to a list of RGB values. The part in the macro where it lists the RGB value to use would come from that input.

Any help would be appreciated, I've never used a message box or input box before. Thank you!

 

[John Davis]

Hi All,

I'd like to add a message box to the following macro.

Sub FormatDataPoints()
 
Dim i As Long

    With ActiveSheet.ChartObjects("Chart 1").Chart
        For i = 1 To 10
            With .SeriesCollection(i)
                .MarkerStyle = 2
                .MarkerSize = 7
                .Format.Fill.ForeColor.RGB = RGB(0, 128, 0)
                .Format.Line.Visible = msoFalse
                .ApplyDataLabels
                    With .DataLabels
                        .ShowSeriesName = True
                        .ShowValue = False
                        .Position = Above
                        .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 128, 0)
                    End With
            End With
        Next i
    End With
 
End Sub

Basically, I'd like to have a message box come up that asks the user to define which series the formatting should be applied to. So, the line in the macro where it says For i = x to y would be defined by the message box.

I'd also like to have a message box that would allow a user to select what color they wanted to use for the formatting, with the names matched to a list of RGB values. The part in the macro where it lists the RGB value to use would come from that input.

Any help would be appreciated, I've never used a message box or input box before. Thank you!

For the first part of your question, would something like this be what you want?

Sub FormatDataPoints()
 
Dim i As Long
Dim x As Integer
Dim y As Integer

x = InputBox("Please Enter the Starting Number")

y = InputBox("Please Enter the Ending Number")

    With ActiveSheet.ChartObjects("Chart 1").Chart
    
        For i = x To y
        
            With .SeriesCollection(i)
                .MarkerStyle = 2
                .MarkerSize = 7
                .Format.Fill.ForeColor.RGB = RGB(0, 128, 0)
                .Format.Line.Visible = msoFalse
                .ApplyDataLabels
                    With .DataLabels
                        .ShowSeriesName = True
                        .ShowValue = False
                        .Position = Above
                        .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 128, 0)
                    End With
            End With
        Next i
    End With
 
End Sub

 

[ttbuson]

Yes! That is exactly what I was looking for. Thank you so much for this; you are awesome. Any advice for the second part?

 

[John Davis]

Yes! That is exactly what I was looking for. Thank you so much for this; you are awesome. Any advice for the second part?

You're welcome. Glad too help out, and thanks for the feedback. I'll be off the board until tomorrow, and if no one else helps you with part two by then, I'll see what I can do.