Please help a newbie to VB!!!

[certain_death]

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

 

[colofnature]

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

 

[Guest]

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

 

[John]

Mark,

Have a go with this:

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

Best regards

John

"certain_death" <[email protected]>
wrote in message
news:[email protected]...

 

[Bob Phillips]

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


--
HTH

Bob Phillips

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

"certain_death" <[email protected]>
wrote in message
news:[email protected]...

 

[certain_death]

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

 

[colofnature]

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


C

 

[certain_death]

Hi Col

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

Am I doing anything wrong?

Thanks
Mark

 

[colofnature]

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.

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[certain_death]

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

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...