Posted to microsoft.public.excel.programming
|
|
Form, or seperate text boxes?
Then use something like
Dim ans
Dim dteFirst As Date, dteSecond As Date
Dim IsValidDate As Boolean
IsValidDate = False
Do
ans = InputBox("Please supply first date")
If ans = "" Then
IsValidDate = True
Else
On Error Resume Next
dteFirst = CDate(ans)
On Error GoTo 0
If dteFirst < 0 Then
IsValidDate = True
Else
MsgBox "Invalid date", vbInformation + vbOK
End If
End If
Loop Until IsValidDate
IsValidDate = False
Do
ans = InputBox("Please supply second date")
If ans = "" Then
IsValidDate = True
Else
On Error Resume Next
dteSecond = CDate(ans)
On Error GoTo 0
If dteSecond < 0 Then
IsValidDate = True
Else
MsgBox "Invalid date", vbInformation + vbOK
End If
End If
Loop Until IsValidDate
'now use those values
--
HTH
Bob Phillips
(replace xxxx in email address with googlemail if mailing direct)
"pianoman" wrote in
message ...
Ah, i see what you mean... Selecting the name that way... A list, or
combobox or something would be better, as the end users will expect to
have everything laid in front of them, all in the same place when they
click the update button, but I see what you mean now... that would do
the job for the name part.
The dates will need to come from the user, they will need to input
two dates, after they've selected the name, and then the dates that
they enter, stored and assigned to variables, so that I can use them
later. It's really only this Input part that I'm struggling with.
Once I've got the Name.row, Date1 and Date2 variables defined from the
User Input, I'm good to go!
Thanks Bob,
Bob Phillips Wrote:
Gareth,
The code that I gave you assumed that you would select the name in
question,
and then click the button to run the macro. Therefore the activecell
would
contain your selected name, and code like
ACtivecell.Offset(0,1).Value
gets the value from column B (1 column right of activecell) in that
same
row. Likewise, you can set a value with
Activecell.Offset(0,1).Value = date
which puts today's date in there. You mention inserting dates, do you
need
to get those dates from the user, somewhere else, or is it today's
date.
Moving onto to the master sheet, you mention relevant columns. We can
do
that with something like
With Activecell
Worksheets("Master").Cells(.Row,"H").Value = .Offset(0,1).Value
End With
What this does is copy the value in column B of the selected name to
column
H of the Master sheet.
Is this any better?
--
HTH
Bob Phillips
(remove xxx from email address if mailing direct)
"pianoman"
wrote in
message ...
Ok, cool so I can run that with a button click, but it's still not
doing
what I need... how about the input fields bit referring to the other
sheet?
I need to be able to choose a name from a list (sourced from
column
a),
presumably record the row number for that name or something, then
input
dates into two different input fields, record those values and
then
run
seperate code to jump to the master data sheet, enter those values
into
the relevant columns in the row number recorded from the form
earlier.
Does the code you've given me get me closer to that goal?
Thanks Bob, appreciate your help.
Gareth
Bob Phillips Wrote:
You could attach a similar bit of code to a button, and use it like
so
when
you click the button
Sub MyMacro()
With Activecell
If .Column = 1 Then
'you can now get other fields
MsgBox .Offset(0, 1) 'column B
MsgBox .Offset(0, 2) 'column C
MsgBox .Offset(0, 3) 'column D
'etc.
End If
End With
End Sub
--
HTH
Bob Phillips
(remove xxx from email address if mailing direct)
"pianoman"
wrote in
message
...
Hi Bob,
Thanks again, that works fine when the data is there already...
However I need it to do what ever it's going to do on a button
click
really. I run code to refresh the sheet with data from several
sources, and at the moment, the Worksheet_selectionchange part
is
kicking in before the data is refreshed, which crashes the
macro.
Also, I'm not sure this is going to solve my problem is it? let
me
re-iterate in case I wasn't clear first time round! :)
I need to be able to choose a name from a list (sourced from
column
a),
presumably record the row number for that name or something,
then
input
dates into two different input fields, record those values and
then
run
seperate code to jump to the master data sheet, enter those
values
into
the relevant columns in the row number recorded from the form
earlier.
Does the code you've given me get me closer to that goal?
Thanks
--
pianoman
------------------------------------------------------------------------
pianoman's Profile:
http://www.excelforum.com/member.php...o&userid=33712
View this thread:
http://www.excelforum.com/showthread...hreadid=544994
--
pianoman
------------------------------------------------------------------------
pianoman's Profile:
http://www.excelforum.com/member.php...o&userid=33712
View this thread:
http://www.excelforum.com/showthread...hreadid=544994
--
pianoman
------------------------------------------------------------------------
pianoman's Profile:
http://www.excelforum.com/member.php...o&userid=33712
View this thread: http://www.excelforum.com/showthread...hreadid=544994
|