We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Restrict User Input Box to numeric values between 1 and 10

Andreas Hermle
on
Medium Priority
244 Views
Last Modified: 2012-05-11
Dear Experts:

below macro lets user change the font size of a chart's data labels by means of an Input Box.

I would like the inputbox part of the macro expanded in the following way:

... only numeric values between 1 and 10 are allowed (ie. Font size of the data labels)
... If above condition is not met the inputbox dialog is to re-appear. In the current state the macro exits on non-numeric values.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Dim Chtobj As ChartObject
Dim i As Integer
Dim ser As Series
Dim DefineFontSize As String
i = 0
  
      If MsgBox("Would you like to alter the font size of the data labels ?", vbQuestion + vbYesNo, "Change Font Size Data Labels") = vbNo Then
Exit Sub
End If

DefineFontSize = InputBox("Specifiy the new Font Size of the Data Labels!")
If Not IsNumeric(DefineFontSize) Then
MsgBox ("Only numeric values are allowed!")
Exit Sub
End If
If DefineFontSize = "" Then
Exit Sub
End If

For Each Chtobj In ActiveSheet.ChartObjects
    With Chtobj.Chart
        For Each ser In .SeriesCollection
                   ser.ApplyDataLabels
            With ser.DataLabels
                        .ShowCategoryName = True
            .Separator = "" & chr(10) & ""
            .Position = xlLabelPositionOutsideEnd
               .Font.Color = RGB(5, 5, 5)
               .Font.Name = "Verdana"
               .Font.Bold = True
               .Font.Size = DefineFontSize
           End With
        Next ser
  End With
Next
End Sub

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
You could use:
   Do
      definefontsize = InputBox("Specify the new Font Size of the Data Labels!")
      If definefontsize >= 1 And definefontsize <= 10 Then
         Exit Do
      Else
         MsgBox "Invalid data - must be between 1 and 10!"
      End If
   Loop Until definefontsize = ""

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
PS That would replace lines 11-18 of your original.
Andreas HermleTeam leader

Author

Commented:
Hi rorya:
thank you very much for your swift support. I guess, we are almost there.

If the user presses 'Cancel' on the InputBox dialog field the macro throws a Runtime Error '13'
If the user enters a non-numeric value the Runtime Error '13' is also triggered.

Is it possible to tweak the macro accordingly?

Thank you very much in advance for your kind help.

Regards, Andreas
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Andreas HermleTeam leader

Author

Commented:
Hi rorya:

ok, great that's it. I tweaked it a little bit so that the macro exits when users do not enter anything and press 'Cancel' right away.

Do
      DefineFontSize = InputBox("Specify the new Font Size of the Data Labels!")
      If DefineFontSize = "" Then
      Exit Sub
      End If
      If IsNumeric(DefineFontSize) Then
       If DefineFontSize >= 1 And DefineFontSize <= 15 Then
         Exit Do
      Else
         MsgBox "Invalid data - must be between 1 and 15!"
      End If
     Else
            MsgBox "Invalid data - must be between 1 and 15!"
      End If
   Loop Until DefineFontSize = ""
Andreas HermleTeam leader

Author

Commented:
Always very swift and professional support from your side. I really appreciate it.

Regards, Andreas
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.