365 excel application.inputbox problems on win 10

Question

winin:

                    lnWinsDue = Application.InputBox("Please enter winnings for " & WinArray(lnArrayRow, 2), _

                    Type:=1, Title:=gcMsgTitle, Default:=0)

                    Select Case lnWinsDue

                        Case False

                            GoTo theend

                        Case Is < 0.5

                            lnans = MsgBox("It's too small. Do you want to try again", vbYesNo)

                            If lnans = 7 Then GoTo theend Else GoTo winin

                        Case Is >= lnWinningsPaid

                            lnans = MsgBox("It's too big. Do you want to try again", vbYesNo)

                            If lnans = 7 Then GoTo theend Else GoTo winin

                    End Select

                    If lnWinsDue / 0.5 <> Int(lnWinsDue / 0.5) Then

                        lnans = MsgBox("Your entry must be a multiple of £0.05. Do you want to try again", vbYesNo)

                        If lnans = 7 Then GoTo theend Else GoTo winin

                    End If

I chose to use the Method rather than the Function because it seemed to offer more control over the input, however it produced problems.

1 my first attempt did not include a Default parameter. The response to a null entry produced the error msg below. As I have secified Type=1 this seems odd.

2 I got rid of it by introducing Default=0. However if the 0 is accepted my code exits under Case False despite the fact that the var lnWinsDue =0.

3 The input box shows a "?" in the header. When selected this launches a web page on "Select a range to import to Visio". Very interesting but irrelevant. I tried using the HelpFile parameter with enties like HelpFile:-"" but got the same result.

Other that that it seems to work when normal inputs are used. Trouble is that users don't behave that way.

Answer 1

seems a bit expensive as i live in the UK. i presume i could go on line instead?

Answer 2

Hi michaelcoltart,

Thank you for your response. We highly recommend posting this concern to Microsoft Developer forums to better check the macro scripts that you compiled.

Please see the forum links below:

https://social.msdn.microsoft.com/Forums/en-US/...

https://developer.microsoft.com/en-us/excel/ove...

https://social.technet.microsoft.com/Forums/off...

Hope this helps.

Sincerely,

Edmund A.

Independent Advisor

Answer 3

I am using macros in Excel 365 Home. I need the user to input a number. On previous occassions I had used the InputBox Function which worked well with strings. Recently I discovered the Application.InputBox method and noted it could specify the input data type. It does work and rejects any non-numeric entries. However it brings other complications. I want to prevent any unsceduled events. The input should cope with whatever the user throws at it, such as an "Enter" without any value (eqivalent to a blank string) in the Box, entering 0 or any other unwanted number. I though my code covered that but the results are not as expected. The Cancel button returns False as expected. But 0 is also detected as False despite the var lnWinsDue holding 0. OK as long as 0 is not an acceptable input but certainly not what one would expect. I found out about 0 because a null entry initiated the message box I pasted to my initial post. I found that specifying a default value prevented that unwanted intrusion. My other issue is the "?" that appears in the inputbox header. As described it initiates the web browser and I cannot find a way of stopping it..

It seems that this Method is a bit cranky but I had hoped someone had travelled this road before me. If I am forced to go back to the standard input box I will have to write code to ensure I get only numeric values input. Messy!

Hope this clarifies my problem

Answer 4

Hi michaelcoltart,

I'm Ed, a fellow customer like you & an Independent Advisor.

I understand that you are having problems with the formulas in Excel.

Can you please provide more information for us to be able to diagnose the issue?

1. What is it that you are trying to accomplish with the formulas below? Are you using Macros?
2. What is the version of the Excel that you are using?

Thank you!

Sincerely,

Edmund A.

Independent Advisor