I've searched the previous posts trying to find an answer to this question -
no luck. Is there a way to get spell check to work when the text is in a
text box? Currently, it will only check any words outside of this area.

Thanks you in advance.

Lee Ann -

This surprised the heck out of me, but in Excel 2007 when I recorded
the process of spell-checking one textbox and then played a bit more it
looks like this simple sub will do what you need:

Public Sub SpellCheckTextboxes()

Cells.CheckSpelling SpellLang:=1033

End Sub

It seems to start at Cell A1 and then works its way down and to the
right, looking for textboxes and spell-checking the text in them.

Please let me know if this works for you...


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599

I'm looking forward to trying this, but can you offer some direction on how
to use it? I can get around Excel pretty well, but have never used this.

Excuse my ignorance, but I assume what you've written should be copied and
after right clicking the affected Excel workbook page, I should choose View
Code and paste it there? If this is correct, I'm unsure where to go from
there. Thanks in advance.

"jamescox" wrote:


Lee Ann -

This surprised the heck out of me, but in Excel 2007 when I recorded
the process of spell-checking one textbox and then played a bit more it
looks like this simple sub will do what you need:

Public Sub SpellCheckTextboxes()

Cells.CheckSpelling SpellLang:=1033

End Sub

It seems to start at Cell A1 and then works its way down and to the
right, looking for textboxes and spell-checking the text in them.

Please let me know if this works for you...


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599

The code goes into a code module, not a sheet's code or the
ThisWorkbook module.

If that didn't make a lot of sense, you need to get into Visual Basic
from the Developer tab on the ribbon menu. (If you don't have a
Devloper tab, click on the Office orb - that round button at the top
left of the Excel window, select Excel Options and then on the 'Popular
window, check the third box down Show developer tab in the ribbon).

On the left hand side of the VB editor window, there should be Project
- VBA Project pane and it should have listed your workbook as a VBA
project. Right click on the VBA project that has your workbook name in
parentheses and from the pop up menu select Insert and then Module.
This should open up a code window in the editor - and that's where you
paste the code; it will be a macro.

To run the macro, go to the View tab on the ribbon, click on the Macro
button and from the window that pops up, select the SpellCheckTextboxes
item and click on Run.

That should get you there!
To run it, select the worksheet you want to spell-check


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599

jamescox;439827 Wrote:
The code goes into a code module, not a sheet's code or the ThisWorkbook
module.

If that didn't make a lot of sense, you need to get into Visual Basic
from the Developer tab on the ribbon menu. (If you don't have a Devloper
tab, click on the Office orb - that round button at the top left of the
Excel window, select Excel Options and then on the 'Popular window,
check the third box down Show developer tab in the ribbon).

On the left hand side of the VB editor window, there should be Project
- VBA Project pane and it should have listed your workbook as a VBA
project. Right click on the VBA project that has your workbook name in
parentheses and from the pop up menu select Insert and then Module. This
should open up a code window in the editor - and that's where you paste
the code; it will be a macro.

To run the macro, go to the View tab on the ribbon, click on the Macro
button and from the window that pops up, select the SpellCheckTextboxes
item and click on Run.

That should get you there!
To run it, select the worksheet you want to spell-check

*How to add and run a Macro*1. *Copy* the macro above pressing
the keys *CTRL+C*
2. Open your workbook
3. Press the keys *ALT+F11* to open the Visual Basic Editor
4. Press the keys *ALT+I* to activate the *Insert menu*
5. *Press M* to insert a *Standard Module*
6. *Paste* the code by pressing the keys *CTRL+V*
7. Make any custom changes to the macro if needed at this time.
8. *Save the Macro* by pressing the keys *CTRL+S*
9. Press the keys *ALT+Q* to exit the Editor, and return to Excel.

*To Run the Macro...*
To run the macro from Excel, open the workbook, and press *ALT+F8* to
display the *Run Macro Dialog*. Double Click the macro's name to *Run*
it.


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599

James & Simon - I was able to follow the instructions and get the code in as
you said. I'm obviously missing something as I received the following error
message:

Run-time error "1004": CheckSpelling method of Range class failed

I am not experienced in doing this type of thing so I do apologize for all
the questions and problems. Thanks for all your help so far and hopefully
you can assist with this latest error.

"Simon Lloyd" wrote:


jamescox;439827 Wrote:
The code goes into a code module, not a sheet's code or the ThisWorkbook
module.

If that didn't make a lot of sense, you need to get into Visual Basic
from the Developer tab on the ribbon menu. (If you don't have a Devloper
tab, click on the Office orb - that round button at the top left of the
Excel window, select Excel Options and then on the 'Popular window,
check the third box down Show developer tab in the ribbon).

On the left hand side of the VB editor window, there should be Project
- VBA Project pane and it should have listed your workbook as a VBA
project. Right click on the VBA project that has your workbook name in
parentheses and from the pop up menu select Insert and then Module. This
should open up a code window in the editor - and that's where you paste
the code; it will be a macro.

To run the macro, go to the View tab on the ribbon, click on the Macro
button and from the window that pops up, select the SpellCheckTextboxes
item and click on Run.

That should get you there!
To run it, select the worksheet you want to spell-check

*How to add and run a Macro*1. *Copy* the macro above pressing
the keys *CTRL+C*
2. Open your workbook
3. Press the keys *ALT+F11* to open the Visual Basic Editor
4. Press the keys *ALT+I* to activate the *Insert menu*
5. *Press M* to insert a *Standard Module*
6. *Paste* the code by pressing the keys *CTRL+V*
7. Make any custom changes to the macro if needed at this time.
8. *Save the Macro* by pressing the keys *CTRL+S*
9. Press the keys *ALT+Q* to exit the Editor, and return to Excel.

*To Run the Macro...*
To run the macro from Excel, open the workbook, and press *ALT+F8* to
display the *Run Macro Dialog*. Double Click the macro's name to *Run*
it.


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599

Lee, use the same procedure as above to save it in your workbook (delete
the old one completely) and save this:
Code:
--------------------
Public Sub SpellCheckTextboxes()
ActiveSheet.UsedRange.CheckSpelling SpellLang:=1033
End Sub

--------------------
Lee Ann;440843 Wrote:
James & Simon - I was able to follow the instructions and get the code
in as
you said. I'm obviously missing something as I received the following
error
message:

Run-time error "1004": CheckSpelling method of Range class failed

I am not experienced in doing this type of thing so I do apologize for
all
the questions and problems. Thanks for all your help so far and
hopefully
you can assist with this latest error.

"Simon Lloyd" wrote:


jamescox;439827 Wrote:
The code goes into a code module, not a sheet's code or the
ThisWorkbook
module.

If that didn't make a lot of sense, you need to get into Visual
Basic
from the Developer tab on the ribbon menu. (If you don't have a
Devloper
tab, click on the Office orb - that round button at the top left of
the
Excel window, select Excel Options and then on the 'Popular window,
check the third box down Show developer tab in the ribbon).

On the left hand side of the VB editor window, there should be
Project
- VBA Project pane and it should have listed your workbook as a VBA
project. Right click on the VBA project that has your workbook name
in
parentheses and from the pop up menu select Insert and then Module.
This
should open up a code window in the editor - and that's where you
paste
the code; it will be a macro.

To run the macro, go to the View tab on the ribbon, click on the
Macro
button and from the window that pops up, select the
SpellCheckTextboxes
item and click on Run.

That should get you there!
To run it, select the worksheet you want to spell-check

*How to add and run a Macro*1. *Copy* the macro above pressing
the keys *CTRL+C*
2. Open your workbook
3. Press the keys *ALT+F11* to open the Visual Basic Editor
4. Press the keys *ALT+I* to activate the *Insert menu*
5. *Press M* to insert a *Standard Module*
6. *Paste* the code by pressing the keys *CTRL+V*
7. Make any custom changes to the macro if needed at this time.
8. *Save the Macro* by pressing the keys *CTRL+S*
9. Press the keys *ALT+Q* to exit the Editor, and return to Excel.

*To Run the Macro...*
To run the macro from Excel, open the workbook, and press *ALT+F8* to
display the *Run Macro Dialog*. Double Click the macro's name to
*Run*
it.


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' ('The Code Cage - Microsoft Office Help -
Microsoft Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Spell Check in Text Boxes - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=120599)




--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599

I was having issues with this, but determined I still had the page protected.
The spell check works, however still ignores the text boxes. Any other idea
you have would be much appreciated.

"Simon Lloyd" wrote:


Lee, use the same procedure as above to save it in your workbook (delete
the old one completely) and save this:
Code:
--------------------
Public Sub SpellCheckTextboxes()
ActiveSheet.UsedRange.CheckSpelling SpellLang:=1033
End Sub

--------------------
Lee Ann;440843 Wrote:
James & Simon - I was able to follow the instructions and get the code
in as
you said. I'm obviously missing something as I received the following
error
message:

Run-time error "1004": CheckSpelling method of Range class failed

I am not experienced in doing this type of thing so I do apologize for
all
the questions and problems. Thanks for all your help so far and
hopefully
you can assist with this latest error.

"Simon Lloyd" wrote:


jamescox;439827 Wrote:
The code goes into a code module, not a sheet's code or the
ThisWorkbook
module.

If that didn't make a lot of sense, you need to get into Visual
Basic
from the Developer tab on the ribbon menu. (If you don't have a
Devloper
tab, click on the Office orb - that round button at the top left of
the
Excel window, select Excel Options and then on the 'Popular window,
check the third box down Show developer tab in the ribbon).

On the left hand side of the VB editor window, there should be
Project
- VBA Project pane and it should have listed your workbook as a VBA
project. Right click on the VBA project that has your workbook name
in
parentheses and from the pop up menu select Insert and then Module.
This
should open up a code window in the editor - and that's where you
paste
the code; it will be a macro.

To run the macro, go to the View tab on the ribbon, click on the
Macro
button and from the window that pops up, select the
SpellCheckTextboxes
item and click on Run.

That should get you there!
To run it, select the worksheet you want to spell-check

*How to add and run a Macro*1. *Copy* the macro above pressing
the keys *CTRL+C*
2. Open your workbook
3. Press the keys *ALT+F11* to open the Visual Basic Editor
4. Press the keys *ALT+I* to activate the *Insert menu*
5. *Press M* to insert a *Standard Module*
6. *Paste* the code by pressing the keys *CTRL+V*
7. Make any custom changes to the macro if needed at this time.
8. *Save the Macro* by pressing the keys *CTRL+S*
9. Press the keys *ALT+Q* to exit the Editor, and return to Excel.

*To Run the Macro...*
To run the macro from Excel, open the workbook, and press *ALT+F8* to
display the *Run Macro Dialog*. Double Click the macro's name to
*Run*
it.


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' ('The Code Cage - Microsoft Office Help -
Microsoft Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Spell Check in Text Boxes - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=120599)




--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599

Lee Ann -

Sorry, but I can't reproduce your problem in Excel 2007 with either my
original sub or Simon Lloyd's - they both work fine.

As a check, start with a blank workbook, type some misspelled text into
a cell, then throw a few shapes (a textbox and a shape to which you add
misspelled text), add a module and put either or both subs in (don't use
the same name for both, of course) and see if they work.

If they do, there may be some 'residual protection' on the shapes in
your original workbook that's keeping the code from being able to
spell-check them.

Let us know what you find out!


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599

One other thought - check the properties for the text boxes 9i.e locked
etc), also where did you get the textboxes from? from the activex tools?jamescox;443990 Wrote:
Lee Ann -

Sorry, but I can't reproduce your problem in Excel 2007 with either my
original sub or Simon Lloyd's - they both work fine.

As a check, start with a blank workbook, type some misspelled text into
a cell, then throw a few shapes (a textbox and a shape to which you add
misspelled text), add a module and put either or both subs in (don't use
the same name for both, of course) and see if they work.

If they do, there may be some 'residual protection' on the shapes in
your original workbook that's keeping the code from being able to
spell-check them.

Let us know what you find out!


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599

Very sorry for the delay in response - I was never notified there were
additional responses. I tried all ideas listed in your previous two posts.
The text box is under the ActiveX Controls. I checked the properties and
changed anything that gave an indication this was a protected object. I also
put some text in a cell and also added a graphic with some misspelled text.
The spell check worked everywhere except in the text box. I tried both of
the subs and each of them worked the same - still not in the text boxes. I'd
be glad to send what I've done if you're willing to take a look at it.



"Simon Lloyd" wrote:


One other thought - check the properties for the text boxes 9i.e locked
etc), also where did you get the textboxes from? from the activex tools?jamescox;443990 Wrote:
Lee Ann -

Sorry, but I can't reproduce your problem in Excel 2007 with either my
original sub or Simon Lloyd's - they both work fine.

As a check, start with a blank workbook, type some misspelled text into
a cell, then throw a few shapes (a textbox and a shape to which you add
misspelled text), add a module and put either or both subs in (don't use
the same name for both, of course) and see if they work.

If they do, there may be some 'residual protection' on the shapes in
your original workbook that's keeping the code from being able to
spell-check them.

Let us know what you find out!


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599

The fact that these are ActiveX textboxes makes a lot of difference.

On some worksheet in your workbook, define a two-cell named range as
ActiveXText (it can be on the sheet where your ActiveX textboxes are,
but it doesn't have to be there). Note that the 'two cell range'
comment is important - it can be two cells, one above the other or
side-by-side, but it needs to be two cells.


Add this macro

Public Sub SpellCheckActiveXTextBoxes()

Dim aOleTxtBox As OLEObject
Dim rText As Range

Set rText = Range("ActiveXText")

For Each aOleTxtBox In ActiveSheet.OLEObjects

On Error Resume Next
rText.Cells(1).Value = aOleTxtBox.Object.Text
If Err = 0 Then
Application.Goto Reference:=aOleTxtBox.TopLeftCell
rText.Cells.CheckSpelling
aOleTxtBox.Object.Text = rText.Cells(1).Text
MsgBox "Spelling for this ActiveX control has been
checked."
End If
On Error GoTo 0
Next

MsgBox "Done - spelling for all ActiveX controls on this sheet have
been checked!"

End Sub

then go to a sheet that has ActiveX text boxes and run it. It should
move you from ActiveX textbox to ActiveX text box and pop open the Spell
Check form at any ActiveX Textbox that it finds a mispelled word in. If
you don't care for the MsgBox popups, you can comment those out of the
macro.

This hasn't been tested really well with other ActiveX controls other
than the TextBox and the Label. It will display the Spell Check form
for any ActiveX control that has a Text property.

Hope this version works for you (finally!)...


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599

I'm getting a Compile Error: Syntax error. When it brings up the macro
after getting the error, there are changes in what was originally put in -
the first line is highlighted in yellow:

Public Sub SpellCheckActiveXTextBoxes()


It has then put " after the first MsgBox "Spelling for this ActiveX control
has been" and has made the word 'checked' red.

Where the second MsgBox string is, it has again put " after have and shows
the 'been checked' in red.

I've pasted a copy below:

MsgBox "Spelling for this ActiveX control has been"
checked."
End If
On Error GoTo 0
Next

MsgBox "Done - spelling for all ActiveX controls on this sheet have"
been checked!"





"jamescox" wrote:


The fact that these are ActiveX textboxes makes a lot of difference.

On some worksheet in your workbook, define a two-cell named range as
ActiveXText (it can be on the sheet where your ActiveX textboxes are,
but it doesn't have to be there). Note that the 'two cell range'
comment is important - it can be two cells, one above the other or
side-by-side, but it needs to be two cells.


Add this macro

Public Sub SpellCheckActiveXTextBoxes()

Dim aOleTxtBox As OLEObject
Dim rText As Range

Set rText = Range("ActiveXText")

For Each aOleTxtBox In ActiveSheet.OLEObjects

On Error Resume Next
rText.Cells(1).Value = aOleTxtBox.Object.Text
If Err = 0 Then
Application.Goto Reference:=aOleTxtBox.TopLeftCell
rText.Cells.CheckSpelling
aOleTxtBox.Object.Text = rText.Cells(1).Text
MsgBox "Spelling for this ActiveX control has been
checked."
End If
On Error GoTo 0
Next

MsgBox "Done - spelling for all ActiveX controls on this sheet have
been checked!"

End Sub

then go to a sheet that has ActiveX text boxes and run it. It should
move you from ActiveX textbox to ActiveX text box and pop open the Spell
Check form at any ActiveX Textbox that it finds a mispelled word in. If
you don't care for the MsgBox popups, you can comment those out of the
macro.

This hasn't been tested really well with other ActiveX controls other
than the TextBox and the Label. It will display the Spell Check form
for any ActiveX control that has a Text property.

Hope this version works for you (finally!)...


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599

You've got line wrap problems.

The line in my note that reads

MsgBox "Spelling for this ActiveX control has been checked."

has been broken by whatever technique you used to get the subroutine
code into the VBA editor to this:

MsgBox "Spelling for this ActiveX control has been
checked."

where there is a carriage return behind the 'n' in 'been'. Excel saw
that there was a line where a string had been started with a double
quote, but there was no closing quote, so it added one - giving what you
saw, ie

MsgBox "Spelling for this ActiveX control has been"
checked."

However, then it now found on a new line

checked"

at which point, it threw up it's hands, turned the text red (to
indicate it had found a problem) and wouldn't run the sub.

So, just make sure the long lines look like they are in my post and you
should be OK.

(The yellow highlight marks the place in the code where execution
stopped - in this case since it found bad code in the subroutine, it
stopped at the first line of the sub)

Copying the code from the grey box, pasting it into WordPad (not
NotePad) or Word, then recopying it from there and pasting it into the
VBA editor generally will take care of line wrap problems...


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599

Thank you for being so patient - that worked!!

Just another question if it's a quick solution - the document I am using
this on is for evaluating personnel. They are evaluated on a daily basis and
4 forms (workbook pages) are being completed each day for a period of 14
days. The second tab is the only one where the evaluator is writing in the
ActiveX text boxes. There are 10 pages to that document - 2 text boxes per
page (total of 20 text boxes for each tab). This code has it checking all of
the text boxes - is there any simple way to tell it to stop when it sees no
more text? The evaluators usually don't use any more than 4 pages (8 text
boxes total) when they fill this form in.

Again, if it's not simple, don't worry about it - what you've done already
will make alot of people happy.

Thanks so much.

"jamescox" wrote:


You've got line wrap problems.

The line in my note that reads

MsgBox "Spelling for this ActiveX control has been checked."

has been broken by whatever technique you used to get the subroutine
code into the VBA editor to this:

MsgBox "Spelling for this ActiveX control has been
checked."

where there is a carriage return behind the 'n' in 'been'. Excel saw
that there was a line where a string had been started with a double
quote, but there was no closing quote, so it added one - giving what you
saw, ie

MsgBox "Spelling for this ActiveX control has been"
checked."

However, then it now found on a new line

checked"

at which point, it threw up it's hands, turned the text red (to
indicate it had found a problem) and wouldn't run the sub.

So, just make sure the long lines look like they are in my post and you
should be OK.

(The yellow highlight marks the place in the code where execution
stopped - in this case since it found bad code in the subroutine, it
stopped at the first line of the sub)

Copying the code from the grey box, pasting it into WordPad (not
NotePad) or Word, then recopying it from there and pasting it into the
VBA editor generally will take care of line wrap problems...


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599

Not a difficult addition. Just below the line

Dim rText As Range

*add* these lines:

Dim sMsg As String
Dim lResponse As Long

sMsg = "Spelling for this box has been checked." & vbLf & vbLf
sMsg = sMsg & "Click OK to continue or Cancel to end spell checking."

Then, *replace* the line

MsgBox "Spelling for this ActiveX control has been checked."

with these lines:

lResponse = MsgBox(sMsg, vbOKCancel)

If lResponse = vbCancel Then
Exit Sub
End If

That ought to get you there. You can change the text in sMsg to
whatever would make the most sense to your users.

James


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599

This worked, but it was a little unpredictable in the path it chose when
checking the boxes. I'm just going to stick with the original code.

Thanks again for all your assistance!

"jamescox" wrote:


Not a difficult addition. Just below the line

Dim rText As Range

*add* these lines:

Dim sMsg As String
Dim lResponse As Long

sMsg = "Spelling for this box has been checked." & vbLf & vbLf
sMsg = sMsg & "Click OK to continue or Cancel to end spell checking."

Then, *replace* the line

MsgBox "Spelling for this ActiveX control has been checked."

with these lines:

lResponse = MsgBox(sMsg, vbOKCancel)

If lResponse = vbCancel Then
Exit Sub
End If

That ought to get you there. You can change the text in sMsg to
whatever would make the most sense to your users.

James


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599

One of the most important thing in working with spreadsheets is knowing
when to quit! Having said that, if you change the line in the 'original
code' from

If Err = 0 Then

to

If Err = 0 And Len(rText.Cells(1).Value) < 0 Then

your users won't see the code stop for empty textboxes and therefore
they won't have to click OK in for those textboxes.

Now I'm going to put down the keyboard and back slowly away - and maybe
I won't compulsively make any more 'enhancements'... :Bgr


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599

Well, now that you brought that up, I had to try it. I like this one and I
thank you. I suspect always trying to "enhance" makes people such as
yourselves as knowledgeable as you are.

Thanks again!



"jamescox" wrote:


One of the most important thing in working with spreadsheets is knowing
when to quit! Having said that, if you change the line in the 'original
code' from

If Err = 0 Then

to

If Err = 0 And Len(rText.Cells(1).Value) < 0 Then

your users won't see the code stop for empty textboxes and therefore
they won't have to click OK in for those textboxes.

Now I'm going to put down the keyboard and back slowly away - and maybe
I won't compulsively make any more 'enhancements'... :Bgr


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120599