G'Day from sunny Oxford, UK!

Can anyone point me in the right direction please....

I have the code below which kicks off by asking the user to enter a
code which then, if correct, tells another macro to run which sends
mail to users on a list. I have the password because in Excel 97 it
sends the mails so quickly that I don't want to risk users accidentally
clicking on the send button before the mail is ready to go. The passcode
lives in cell P5 on the worksheet.

It works really well in Office 2002, but stops on the second line
starting 'mynum' in Office 97 (OK, so I just changed roles and went
from an XP machine to an NT machine!!!)

I'd really like to leave the passcode in so that the mistakes mentioned
above don't happen, but have no idea how to adapt the code so it runs in
O97.

Any ideas would be very gratefully received.

Regards,

Chris.

-------------------------------------------------------------------------------

Sub send_mail_Click()

mynum = InputBox("Please enter the password to enable the sending of
these e-mails. If you don't know it please contact Big Chris", "You
need a password to proceed...")

If mynum = "" Then
MsgBox "No data entered...", vbInformation, "Validation Code Entry
Error!"
Exit Sub
End If

Sheets("List").Select
Range("p5").Select

If CLng(mynum) = ActiveCell.value Then
Application.OnTime Now, "run_this_macro"

Else
MsgBox "Number is incorrect! Try again or contact ",
vbInformation, "Validation Code Entry Error!"

End If

Sheets("List").Select
Range("ab6").value = mynum

Sheets("List").Select
Range("a1").Select

End Sub


--
Big Chris

Very experienced with excel in general and formulae but just started to
learn VBA....and it's hard!!
------------------------------------------------------------------------
Big Chris's Profile: http://www.excelforum.com/member.php...fo&userid=1350
View this thread: http://www.excelforum.com/showthread...hreadid=396074

Nothing in the code that shouldn't work in xl97 if it works in xl2000.

I would suggest that if you are running this macro from a commandbutton,
that you change the TakeFocusOnClick property to False

--
Regards,
Tom Ogilvy

"Big Chris" wrote
in message ...

G'Day from sunny Oxford, UK!

Can anyone point me in the right direction please....

I have the code below which kicks off by asking the user to enter a
code which then, if correct, tells another macro to run which sends
mail to users on a list. I have the password because in Excel 97 it
sends the mails so quickly that I don't want to risk users accidentally
clicking on the send button before the mail is ready to go. The passcode
lives in cell P5 on the worksheet.

It works really well in Office 2002, but stops on the second line
starting 'mynum' in Office 97 (OK, so I just changed roles and went
from an XP machine to an NT machine!!!)

I'd really like to leave the passcode in so that the mistakes mentioned
above don't happen, but have no idea how to adapt the code so it runs in
O97.

Any ideas would be very gratefully received.

Regards,

Chris.

--------------------------------------------------------------------------
-----

Sub send_mail_Click()

mynum = InputBox("Please enter the password to enable the sending of
these e-mails. If you don't know it please contact Big Chris", "You
need a password to proceed...")

If mynum = "" Then
MsgBox "No data entered...", vbInformation, "Validation Code Entry
Error!"
Exit Sub
End If

Sheets("List").Select
Range("p5").Select

If CLng(mynum) = ActiveCell.value Then
Application.OnTime Now, "run_this_macro"

Else
MsgBox "Number is incorrect! Try again or contact ",
vbInformation, "Validation Code Entry Error!"

End If

Sheets("List").Select
Range("ab6").value = mynum

Sheets("List").Select
Range("a1").Select

End Sub


--
Big Chris

Very experienced with excel in general and formulae but just started to
learn VBA....and it's hard!!
------------------------------------------------------------------------
Big Chris's Profile:
http://www.excelforum.com/member.php...fo&userid=1350
View this thread: http://www.excelforum.com/showthread...hreadid=396074

Thanks for your help Tom - though I think I may be a bit short on
knowledge here!
I can't find TakeFocusOnClick anywhere in my project, so I added it
just before the line where the macro crashes preceded by the button
name i.e. 'button5_click.TakeFocusOnClick = False'.....but now the
macro stops on that line with the error message "Compile Error - Can't
find project or library".

Is there a workaround with regards to validating an entered number
against an entry on the worksheet that would allow me to do the same
thing?

Thanks for you help, it's much appreciate - This time and the previous
times!

Regards,

Chris


--
Big Chris

Very experienced with excel in general and formulae but just started to
learn VBA....and it's hard!!
------------------------------------------------------------------------
Big Chris's Profile: http://www.excelforum.com/member.php...fo&userid=1350
View this thread: http://www.excelforum.com/showthread...hreadid=396074

In design mode (top left button in the control toolbox toolbar), you right
click on the commandbutton and select properties. In the property window,
you find the TakeFocusOnClick property and set it to False.

--
Regards,
Tom Ogilvy

"Big Chris" wrote
in message ...

Thanks for your help Tom - though I think I may be a bit short on
knowledge here!
I can't find TakeFocusOnClick anywhere in my project, so I added it
just before the line where the macro crashes preceded by the button
name i.e. 'button5_click.TakeFocusOnClick = False'.....but now the
macro stops on that line with the error message "Compile Error - Can't
find project or library".

Is there a workaround with regards to validating an entered number
against an entry on the worksheet that would allow me to do the same
thing?

Thanks for you help, it's much appreciate - This time and the previous
times!

Regards,

Chris


--
Big Chris

Very experienced with excel in general and formulae but just started to
learn VBA....and it's hard!!
------------------------------------------------------------------------
Big Chris's Profile:
http://www.excelforum.com/member.php...fo&userid=1350
View this thread: http://www.excelforum.com/showthread...hreadid=396074

Did you try VBA.InputBox instead of InputBox

Haldun Alay

"Big Chris" , haber
iletisinde þunlarý
...

G'Day from sunny Oxford, UK!

Can anyone point me in the right direction please....

I have the code below which kicks off by asking the user to enter a
code which then, if correct, tells another macro to run which sends
mail to users on a list. I have the password because in Excel 97 it
sends the mails so quickly that I don't want to risk users accidentally
clicking on the send button before the mail is ready to go. The passcode
lives in cell P5 on the worksheet.

It works really well in Office 2002, but stops on the second line
starting 'mynum' in Office 97 (OK, so I just changed roles and went
from an XP machine to an NT machine!!!)

I'd really like to leave the passcode in so that the mistakes mentioned
above don't happen, but have no idea how to adapt the code so it runs in
O97.

Any ideas would be very gratefully received.

Regards,

Chris.

-------------------------------------------------------------------------------

Sub send_mail_Click()

mynum = InputBox("Please enter the password to enable the sending of
these e-mails. If you don't know it please contact Big Chris", "You
need a password to proceed...")

If mynum = "" Then
MsgBox "No data entered...", vbInformation, "Validation Code Entry
Error!"
Exit Sub
End If

Sheets("List").Select
Range("p5").Select

If CLng(mynum) = ActiveCell.value Then
Application.OnTime Now, "run_this_macro"

Else
MsgBox "Number is incorrect! Try again or contact ",
vbInformation, "Validation Code Entry Error!"

End If

Sheets("List").Select
Range("ab6").value = mynum

Sheets("List").Select
Range("a1").Select

End Sub


--
Big Chris

Very experienced with excel in general and formulae but just started to
learn VBA....and it's hard!!
------------------------------------------------------------------------
Big Chris's Profile:
http://www.excelforum.com/member.php...fo&userid=1350
View this thread: http://www.excelforum.com/showthread...hreadid=396074