Please help a newbie to VB!!!

Hi all

Can anyone help me please?

I want to be able to run a macro in an excel spreadsheet that will include a pop up msgbox that says "enter a value" and then a user can put a value in and then i want it to put that value in Cell A1 (For Example). This could be either a numeric number or a date field (eg 31/12/2006).
As I am a newbie to this I am struggling. The rest of the macro I have done through recorder in excel, can anyone help me???

Many thanks for reading
Cheers and all the best
Mark

Try:

[a1] = Application.InputBox("Enter a value")

This will accept any value from an inputbox and place it in A1. To force the inputbox to only accept a number:

[a1] = Application.InputBox("Enter a value", , , , , , , 1)

this will display an error message and return FALSE if the value isn't a number.

The first example will display a valid date in the standard format, but will also accept text values. The second will accept dates but convert them into numbers (e.g. 06/06/2006 will be displayed as 38874, so you'd have to tell your macro to format it accordingly)

Hope this helps
Col

Sub GetValue()
Dim myVar as variant

myVar = InputBox("Enter a Value")

Range("A1").value = myVar

End Sub

Or skip the variable:

Sub GetValue()

Range("A1").Value = InputBox("Enter a Value")

End sub

--
Thanks,
Mike


"certain_death" wrote:

>
> Hi all
>
> Can anyone help me please?
>
> I want to be able to run a macro in an excel spreadsheet that will
> include a pop up msgbox that says "enter a value" and then a user can
> put a value in and then i want it to put that value in Cell A1 (For
> Example). This could be either a numeric number or a date field (eg
> 31/12/2006).
> As I am a newbie to this I am struggling. The rest of the macro I have
> done through recorder in excel, can anyone help me???
>
> Many thanks for reading
> Cheers and all the best
> Mark
>
>
>
> --
> certain_death
> ------------------------------------------------------------------------
> certain_death's Profile: http://www.excelforum.com/member.php...o&userid=24561
> View this thread: http://www.excelforum.com/showthread...hreadid=548915
>
>

Mark,

Have a go with this:

Sub InputValue()
Application.ActiveSheet.Range("A1").Value = InputBox("Enter a value")
End Sub

Best regards

John

"certain_death" <certain_death.28zb11_1149594303.9491@excelforum-nospam.com>
wrote in message
news:certain_death.28zb11_1149594303.9491@excelforum-nospam.com...
>
> Hi all
>
> Can anyone help me please?
>
> I want to be able to run a macro in an excel spreadsheet that will
> include a pop up msgbox that says "enter a value" and then a user can
> put a value in and then i want it to put that value in Cell A1 (For
> Example). This could be either a numeric number or a date field (eg
> 31/12/2006).
> As I am a newbie to this I am struggling. The rest of the macro I have
> done through recorder in excel, can anyone help me???
>
> Many thanks for reading
> Cheers and all the best
> Mark
>
>
>
> --
> certain_death
> ------------------------------------------------------------------------
> certain_death's Profile:
> http://www.excelforum.com/member.php...o&userid=24561
> View this thread: http://www.excelforum.com/showthread...hreadid=548915
>

val = InputBox("Enter a value")
Range("A1").Value = val


--
HTH

Bob Phillips

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

"certain_death" <certain_death.28zb11_1149594303.9491@excelforum-nospam.com>
wrote in message
news:certain_death.28zb11_1149594303.9491@excelforum-nospam.com...
>
> Hi all
>
> Can anyone help me please?
>
> I want to be able to run a macro in an excel spreadsheet that will
> include a pop up msgbox that says "enter a value" and then a user can
> put a value in and then i want it to put that value in Cell A1 (For
> Example). This could be either a numeric number or a date field (eg
> 31/12/2006).
> As I am a newbie to this I am struggling. The rest of the macro I have
> done through recorder in excel, can anyone help me???
>
> Many thanks for reading
> Cheers and all the best
> Mark
>
>
>
> --
> certain_death
> ------------------------------------------------------------------------
> certain_death's Profile:
http://www.excelforum.com/member.php...o&userid=24561
> View this thread: http://www.excelforum.com/showthread...hreadid=548915
>

Hey guys

That's brilliant thank you so much...

As a further help (please) can I make the value that is returned in Cell A1 from the input box, copy down the same number of lines in Column A as some corresponding data in Column B. For example, if I had 25 lines of data in column B can I get the value in A1 to copy down to A25 and stop. (This could be a random amount of lines in Col B each time)

Hope I make some sense here..........

Thanks a lot guys and great helpful forum...
Cheers
Mark

range([a1], cells([b1].end(xldown), 1)) = application.inputbox("Enter a value")


C

Hi Col

Am getting "Run Type Error 13" - "Type mismatch"

Am I doing anything wrong?

Thanks
Mark

Without seeing more of your code I dunno... This happens when you're comparing different types of data, e.g.:

if "a string" <> 0 then

If you post up the macro causing the probs I'll have a look.

Dim iLastRow As Long
Dim val

val = InputBox("Enter a value")
Range("A1").Value = val
iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("A1").AutoFill Range("A1").Resize(iLastRow)



--
HTH

Bob Phillips

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

"certain_death" <certain_death.28zehy_1149598804.5545@excelforum-nospam.com>
wrote in message
news:certain_death.28zehy_1149598804.5545@excelforum-nospam.com...
>
> Hey guys
>
> That's brilliant thank you so much...
>
> As a further help (please) can I make the value that is returned in
> Cell A1 from the input box, copy down the same number of lines in
> Column A as some corresponding data in Column B. For example, if I had
> 25 lines of data in column B can I get the value in A1 to copy down to
> A25 and stop. (This could be a random amount of lines in Col B each
> time)
>
> Hope I make some sense here..........
>
> Thanks a lot guys and great helpful forum...
> Cheers
> Mark
>
>
>
> --
> certain_death
> ------------------------------------------------------------------------
> certain_death's Profile:
http://www.excelforum.com/member.php...o&userid=24561
> View this thread: http://www.excelforum.com/showthread...hreadid=548915
>

Hey guys,
That exactly what I need...thanks very much!!!

Am really interested in getting into this sort of thing....could you recommend a good way of learning VB from scratch at beginner level.....

This could help me so much in my job!!

Thanks again
Take it easy!!
Mark

take a look at http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
HTH

Bob Phillips

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

"certain_death" <certain_death.290v9y_1149667202.1256@excelforum-nospam.com>
wrote in message
news:certain_death.290v9y_1149667202.1256@excelforum-nospam.com...
>
> Hey guys,
> That exactly what I need...thanks very much!!!
>
> Am really interested in getting into this sort of thing....could you
> recommend a good way of learning VB from scratch at beginner
> level.....
>
> This could help me so much in my job!!
>
> Thanks again
> Take it easy!!
> Mark
>
>
>
> --
> certain_death
> ------------------------------------------------------------------------
> certain_death's Profile:
http://www.excelforum.com/member.php...o&userid=24561
> View this thread: http://www.excelforum.com/showthread...hreadid=548915
>