[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Restrict User Input Box to numeric values between 1 and 10

Posted on 2011-04-20
6
Medium Priority
?
234 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

0
Comment
Question by:AndreasHermle
  • 3
  • 3
6 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35432679
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

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35432686
PS That would replace lines 11-18 of your original.
0
 

Author Comment

by:AndreasHermle
ID: 35434239
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35434279
Sorry - small addition:
   Do
      definefontsize = InputBox("Specify the new Font Size of the Data Labels!")
      If IsNumeric(definefontsize) then
       If definefontsize >= 1 And definefontsize <= 10 Then
         Exit Do
      Else
         MsgBox "Invalid data - must be between 1 and 10!"
      End If
     Else
            MsgBox "Invalid data - must be between 1 and 10!"
      End If
   Loop Until definefontsize = ""

Open in new window

0
 

Author Comment

by:AndreasHermle
ID: 35434489
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 = ""
0
 

Author Closing Comment

by:AndreasHermle
ID: 35434493
Always very swift and professional support from your side. I really appreciate it.

Regards, Andreas
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question