number input box

[Stan Halls]

i am trying to creat a input box that i can type a number in and when i close
the box that number will be transferd into a cell on the spreadsheet, also i
need to check that the number entered is between to 1 - 6 ,
Any ideas

[Bob Phillips]

Dim ans

Do
ans = Application.InputBox("input a number between 1 and 6)",
Type:=2)
If ans <> False Then
If ans < 1 Or ans > 6 Then
MsgBox "Invalid amount"
End If
End If
Loop Until ans = False Or (ans >= 1 And ans <= 6)
If ans <> False Then
Worksheets("Sheet1").Range("H10").Value = ans
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stan Halls" <StanHalls@discussions.microsoft.com> wrote in message
news:36DB8C67-03CF-4480-8C15-53BE5A64C1B5@microsoft.com...
> i am trying to creat a input box that i can type a number in and when i
close
> the box that number will be transferd into a cell on the spreadsheet, also
i
> need to check that the number entered is between to 1 - 6 ,
> Any ideas
>

[Toppers]

Stan,
Look at Data==>Validation==> select "Whole number" from Allow:
dropdown. You can set range to 1 to 6.


HTH

"Stan Halls" wrote:

> i am trying to creat a input box that i can type a number in and when i close
> the box that number will be transferd into a cell on the spreadsheet, also i
> need to check that the number entered is between to 1 - 6 ,
> Any ideas
>

[Stan Halls]

Perfect, thank you very much for that

"Bob Phillips" wrote:

> Dim ans
>
> Do
> ans = Application.InputBox("input a number between 1 and 6)",
> Type:=2)
> If ans <> False Then
> If ans < 1 Or ans > 6 Then
> MsgBox "Invalid amount"
> End If
> End If
> Loop Until ans = False Or (ans >= 1 And ans <= 6)
> If ans <> False Then
> Worksheets("Sheet1").Range("H10").Value = ans
> End If
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Stan Halls" <StanHalls@discussions.microsoft.com> wrote in message
> news:36DB8C67-03CF-4480-8C15-53BE5A64C1B5@microsoft.com...
> > i am trying to creat a input box that i can type a number in and when i
> close
> > the box that number will be transferd into a cell on the spreadsheet, also
> i
> > need to check that the number entered is between to 1 - 6 ,
> > Any ideas
> >
>
>
>

[Bob Phillips]

Toppers answer is better!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Stan Halls" <StanHalls@discussions.microsoft.com> wrote in message
news:7A733FD4-3C4A-4EB7-81EB-7D25421623E4@microsoft.com...
> Perfect, thank you very much for that
>
> "Bob Phillips" wrote:
>
> > Dim ans
> >
> > Do
> > ans = Application.InputBox("input a number between 1 and 6)",
> > Type:=2)
> > If ans <> False Then
> > If ans < 1 Or ans > 6 Then
> > MsgBox "Invalid amount"
> > End If
> > End If
> > Loop Until ans = False Or (ans >= 1 And ans <= 6)
> > If ans <> False Then
> > Worksheets("Sheet1").Range("H10").Value = ans
> > End If
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Stan Halls" <StanHalls@discussions.microsoft.com> wrote in message
> > news:36DB8C67-03CF-4480-8C15-53BE5A64C1B5@microsoft.com...
> > > i am trying to creat a input box that i can type a number in and when
i
> > close
> > > the box that number will be transferd into a cell on the spreadsheet,
also
> > i
> > > need to check that the number entered is between to 1 - 6 ,
> > > Any ideas
> > >
> >
> >
> >