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
:) :)


--
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

Please help a newbie to VB!!!
 

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


--
colofnature
------------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=548915

Please help a newbie to VB!!!
 

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

Please help a newbie to VB!!!
 

Mark,

Have a go with this:

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

Best regards

John

"certain_death"
wrote in message
news:certain_death.28zb11_1149594303.9491@excelfor um-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

Please help a newbie to VB!!!
 

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


--
HTH

Bob Phillips

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

"certain_death"
wrote in message
news:certain_death.28zb11_1149594303.9491@excelfor um-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

Please help a newbie to VB!!!
 

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

Please help a newbie to VB!!!
 

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


C


--
colofnature
------------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=548915

Please help a newbie to VB!!!
 

Hi Col

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

Am I doing anything wrong?

Thanks
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

Please help a newbie to VB!!!
 

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.


--
colofnature
------------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=548915

Please help a newbie to VB!!!
 

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"
wrote in message
news:certain_death.28zehy_1149598804.5545@excelfor um-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

Please help a newbie to VB!!!
 

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

Please help a newbie to VB!!!
 

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"
wrote in message
news:certain_death.290v9y_1149667202.1256@excelfor um-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