Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with a TextBox?
User opens a book, containing a single sheet. Code in the
Workbook_Open Event runs and if certain conditions are met then a TextBox created by the Drawing toolbar is displayed and enabled for data entry. User enters their data, and when they click a cell outside the textbox (ie exit the textbox) CheckSpelling will automatically run on just the textbox. Having done so, user would be free to re-enter the textbox, but again, CheckSpelling would run when they exit. An ActiveX textbox is fine, except that it doesn't offer the bells and whistles features of Spell Checking. Hope this makes sense. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.727 / Virus Database: 482 - Release Date: 26/07/2004 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with a TextBox?
Stuart
In a standard module Public bSelectTB As Boolean Sub SelectTb() bSelectTB = True Sheet1.Shapes("Text Box 1").Select End Sub Assing SelectTb to the textbox and change this code to reflect the correct sheet and textbox name. Then, in the sheet's module Private Sub Worksheet_SelectionChange(ByVal Target As Range) If bSelectTB Then Me.Shapes("Text Box 1").TopLeftCell.CheckSpelling bSelectTB = False End If End Sub Again change the textbox's name. This worked for me with limited testing, but let me know if you have any problems. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Stuart" wrote in message ... User opens a book, containing a single sheet. Code in the Workbook_Open Event runs and if certain conditions are met then a TextBox created by the Drawing toolbar is displayed and enabled for data entry. User enters their data, and when they click a cell outside the textbox (ie exit the textbox) CheckSpelling will automatically run on just the textbox. Having done so, user would be free to re-enter the textbox, but again, CheckSpelling would run when they exit. An ActiveX textbox is fine, except that it doesn't offer the bells and whistles features of Spell Checking. Hope this makes sense. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.727 / Virus Database: 482 - Release Date: 26/07/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with a TextBox?
Many thanks and apologies for my late response.
I have had problems. When I am reasonably sure of the issues, I will post a new thread, in view of the likely time lapse. The issues are not related to your code, but I believe more related to the way in which I then use the code. Again many thanks. Regards. "Dick Kusleika" wrote in message ... Stuart In a standard module Public bSelectTB As Boolean Sub SelectTb() bSelectTB = True Sheet1.Shapes("Text Box 1").Select End Sub Assing SelectTb to the textbox and change this code to reflect the correct sheet and textbox name. Then, in the sheet's module Private Sub Worksheet_SelectionChange(ByVal Target As Range) If bSelectTB Then Me.Shapes("Text Box 1").TopLeftCell.CheckSpelling bSelectTB = False End If End Sub Again change the textbox's name. This worked for me with limited testing, but let me know if you have any problems. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Stuart" wrote in message ... User opens a book, containing a single sheet. Code in the Workbook_Open Event runs and if certain conditions are met then a TextBox created by the Drawing toolbar is displayed and enabled for data entry. User enters their data, and when they click a cell outside the textbox (ie exit the textbox) CheckSpelling will automatically run on just the textbox. Having done so, user would be free to re-enter the textbox, but again, CheckSpelling would run when they exit. An ActiveX textbox is fine, except that it doesn't offer the bells and whistles features of Spell Checking. Hope this makes sense. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.727 / Virus Database: 482 - Release Date: 26/07/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.727 / Virus Database: 482 - Release Date: 26/07/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with a TextBox?
Stuart wrote:
I will post a new thread, in view of the likely time lapse. Okay. If you like, you can feel free to reply to this thread. I keep a close eye on threads I'm involved with. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with a TextBox?
Resolved, I think. It was a protection issue. Couldn't find a way to
enable just the textbox, so had to unprotect the sheet. Then it worked. I'm now using: Option Explicit Public bSelectTB As Boolean Sub SelectTb() bSelectTB = True Worksheets("Contract Master Fax").Unprotect _ Password:="abc" Sheets("Contract Master Fax") _ .Shapes("Text Box 10").Select End Sub and Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) If bSelectTB Then Me.Shapes("Text Box 10").TopLeftCell.CheckSpelling Worksheets("Contract Master Fax").Protect _ Password:="abc" Worksheets("Contract Master Fax").EnableSelection _ = xlUnlockedCells bSelectTB = False End If End Sub When the user clicks a cell in the sheet to exit the textbox, they can click any cell (since at that point the sheet is unprotected. As long as the Selection_ Change event fires it should be okay and protected again. 3 questions, please: 1. The textbox is positioned over the range "C26:L65". When the user exits, CheckSpelling runs on the textbox, then prompts to check the rest of the sheet. Can this be turned off? 2. When the user initially clicks within the textbox, the cursor is not visible. It's only after a second click that it appears. Can it appear the first time? 3. Can the tab key be enabled? Many thanks again. Regards. "Dick Kusleika" wrote in message ... Stuart wrote: I will post a new thread, in view of the likely time lapse. Okay. If you like, you can feel free to reply to this thread. I keep a close eye on threads I'm involved with. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.727 / Virus Database: 482 - Release Date: 27/07/2004 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with a TextBox?
"Stuart" wrote in message ... 3 questions, please: 1. The textbox is positioned over the range "C26:L65". When the user exits, CheckSpelling runs on the textbox, then prompts to check the rest of the sheet. Can this be turned off? Unfortunately, no. See below for another option. 2. When the user initially clicks within the textbox, the cursor is not visible. It's only after a second click that it appears. Can it appear the first time? I hadn't realized that that was happening, but I confirmed that it does. I tried various things to get it show, but couldn't come up with anything. 3. Can the tab key be enabled? Inside the textbox? Control+tab can be used to tab inside the textbox. As you've seen, there are some drawbacks to this spell check method. I found a few more, like deselecting the textbox by selecting a protected cell causes an error. That error can be trapped, I think, but it's a flaw in the code that I gave you. We're using the CheckSpelling method on a range object. You can also use the CheckSpelling method of the Application object which will get rid of some of those problems. However, it presents a whole new set of problems and some additional coding. For instance, it only checks one word at a time, so you have to split the text from the textbox into words and run the method on those words. Doing this does not show the dialog with the suggestions, it merely returns a True or False depending on the outcome of the check. Let me know if you want to investigate this alternative method. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with a TextBox?
Thanks again.
I originally tried an ActiveX textbox (which seemed fine to me), until my co-users insisted they wanted to run Spelling (F7) on it. I then found that CheckSpelling won't work on that type of textbox. I think I found code for the technique you mention (ie to split the textbox contents into individual words, and then to checkspell them), but as you say, there are no helpful suggestions for anything mispelled. That's the main reason I changed to a textbox from the Drawing toolbar....and I'm beginning to discover some of the limitations! One last question please: How can I obtain a list of the Event options associated with a textbox .... (and that goes for Objects/Controls generally)? I'm sure I've seen a way to do this as soon as the textbox (eg) is created? So if I found a (say) Textbox_Deactivate or Deselect event, I might be able to use that to check the cell the user had exited to, and test that for being locked ...... if so, return user to the textbox. Regards and thanks. "Dick Kusleika" wrote in message ... "Stuart" wrote in message ... 3 questions, please: 1. The textbox is positioned over the range "C26:L65". When the user exits, CheckSpelling runs on the textbox, then prompts to check the rest of the sheet. Can this be turned off? Unfortunately, no. See below for another option. 2. When the user initially clicks within the textbox, the cursor is not visible. It's only after a second click that it appears. Can it appear the first time? I hadn't realized that that was happening, but I confirmed that it does. I tried various things to get it show, but couldn't come up with anything. 3. Can the tab key be enabled? Inside the textbox? Control+tab can be used to tab inside the textbox. As you've seen, there are some drawbacks to this spell check method. I found a few more, like deselecting the textbox by selecting a protected cell causes an error. That error can be trapped, I think, but it's a flaw in the code that I gave you. We're using the CheckSpelling method on a range object. You can also use the CheckSpelling method of the Application object which will get rid of some of those problems. However, it presents a whole new set of problems and some additional coding. For instance, it only checks one word at a time, so you have to split the text from the textbox into words and run the method on those words. Doing this does not show the dialog with the suggestions, it merely returns a True or False depending on the outcome of the check. Let me know if you want to investigate this alternative method. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.727 / Virus Database: 482 - Release Date: 27/07/2004 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with a TextBox?
"Stuart" wrote in message ... One last question please: How can I obtain a list of the Event options associated with a textbox .... (and that goes for Objects/Controls generally)? I'm sure I've seen a way to do this as soon as the textbox (eg) is created? Controls from the Forms or Drawing toolbars don't have events, only OnAction (basically a click event). Only ActiveX controls have events like Deactivate. If you want to avoid that specific error, I think you only need to put your Unprotect line in the SelectionChange event. That will allow the user to select a protected cell, but once the EnableSelection line is run it will kick them to the next unprotected cell. I didn't test that, but I think it will work. If you need to find the event associated with ActiveX controls, the easiest way is to open the sheet module that contains the controls. Use the left pulldown to find the control and right pulldown will show all the events. So if I found a (say) Textbox_Deactivate or Deselect event, I might be able to use that to check the cell the user had exited to, and test that for being locked ...... if so, return user to the textbox. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with a TextBox?
Many thanks for all the help.
Regards. "Dick Kusleika" wrote in message ... "Stuart" wrote in message ... One last question please: How can I obtain a list of the Event options associated with a textbox .... (and that goes for Objects/Controls generally)? I'm sure I've seen a way to do this as soon as the textbox (eg) is created? Controls from the Forms or Drawing toolbars don't have events, only OnAction (basically a click event). Only ActiveX controls have events like Deactivate. If you want to avoid that specific error, I think you only need to put your Unprotect line in the SelectionChange event. That will allow the user to select a protected cell, but once the EnableSelection line is run it will kick them to the next unprotected cell. I didn't test that, but I think it will work. If you need to find the event associated with ActiveX controls, the easiest way is to open the sheet module that contains the controls. Use the left pulldown to find the control and right pulldown will show all the events. So if I found a (say) Textbox_Deactivate or Deselect event, I might be able to use that to check the cell the user had exited to, and test that for being locked ...... if so, return user to the textbox. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.727 / Virus Database: 482 - Release Date: 26/07/2004 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I do this with a TextBox?
Stuart
See Andy's comments here http://www.dicks-blog.com/excel/2004...heck_a_te.html It seems that setting DisplayAlerts to False will prevent that "check rest of sheet" message. I haven't tested it, but Andy's pretty solid. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Stuart" wrote in message ... Many thanks for all the help. Regards. "Dick Kusleika" wrote in message ... "Stuart" wrote in message ... One last question please: How can I obtain a list of the Event options associated with a textbox .... (and that goes for Objects/Controls generally)? I'm sure I've seen a way to do this as soon as the textbox (eg) is created? Controls from the Forms or Drawing toolbars don't have events, only OnAction (basically a click event). Only ActiveX controls have events like Deactivate. If you want to avoid that specific error, I think you only need to put your Unprotect line in the SelectionChange event. That will allow the user to select a protected cell, but once the EnableSelection line is run it will kick them to the next unprotected cell. I didn't test that, but I think it will work. If you need to find the event associated with ActiveX controls, the easiest way is to open the sheet module that contains the controls. Use the left pulldown to find the control and right pulldown will show all the events. So if I found a (say) Textbox_Deactivate or Deselect event, I might be able to use that to check the cell the user had exited to, and test that for being locked ...... if so, return user to the textbox. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.727 / Virus Database: 482 - Release Date: 26/07/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Textbox value based on another textbox value.doc | Excel Discussion (Misc queries) | |||
Calculate Textbox value based on another textbox value | Excel Discussion (Misc queries) | |||
getting textbox value | Excel Programming | |||
to textbox | Excel Programming | |||
UserForm TextBox to ActiveSheet TextBox over 256 characters | Excel Programming |