| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
set focus to form
How would you set focus back to a form once a new cell has been clicked ?
Iv tried the following but no luck Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.TextBox1.SetFocus End Sub Any ideas? -- thank You gus |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
set focus to form
Hi ,
Do you mean you want the form to show? Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.Show End Sub -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Gus Chuch" wrote: How would you set focus back to a form once a new cell has been clicked ? Iv tried the following but no luck Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.TextBox1.SetFocus End Sub Any ideas? -- thank You gus |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
set focus to form
No the form is already shown. I show the form in the workbook open sub. i
just would like to have the textbox get focus again when I click on a new cell. -- thank You "sebastienm" wrote: Hi , Do you mean you want the form to show? Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.Show End Sub -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Gus Chuch" wrote: How would you set focus back to a form once a new cell has been clicked ? Iv tried the following but no luck Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.TextBox1.SetFocus End Sub Any ideas? -- thank You gus |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
set focus to form
Okay, this is a kludge, I mean a **real** kludge, but it does seem to work.
Copy/paste this event code into the code window for the WorkBook.... Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) UserForm1.Show If UserForm1.ActiveControl.TabIndex < UserForm1.TextBox1.TabIndex Then SendKeys "+{TAB " & UserForm1.ActiveControl.TabIndex & "}", True Else SendKeys "{TAB}", True SendKeys "+{TAB}", True End If End Sub I'm sure there must be a "cleaner" way to do this, and hopefully someone will come along with that solution; but, until then, the above should at least let you continue working. Rick "Gus Chuch" wrote in message ... No the form is already shown. I show the form in the workbook open sub. i just would like to have the textbox get focus again when I click on a new cell. -- thank You "sebastienm" wrote: Hi , Do you mean you want the form to show? Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.Show End Sub -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Gus Chuch" wrote: How would you set focus back to a form once a new cell has been clicked ? Iv tried the following but no luck Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.TextBox1.SetFocus End Sub Any ideas? -- thank You gus |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
set focus to form
Not that it is all that critical, but we can condense the two statements in
the Else block down to a single statement... Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) UserForm1.Show If UserForm1.ActiveControl.TabIndex < UserForm1.TextBox1.TabIndex Then SendKeys "+{TAB " & UserForm1.ActiveControl.TabIndex & "}", True Else SendKeys "{TAB}+{TAB}", True End If End Sub Rick "Rick Rothstein (MVP - VB)" wrote in message ... Okay, this is a kludge, I mean a **real** kludge, but it does seem to work. Copy/paste this event code into the code window for the WorkBook.... Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) UserForm1.Show If UserForm1.ActiveControl.TabIndex < UserForm1.TextBox1.TabIndex Then SendKeys "+{TAB " & UserForm1.ActiveControl.TabIndex & "}", True Else SendKeys "{TAB}", True SendKeys "+{TAB}", True End If End Sub I'm sure there must be a "cleaner" way to do this, and hopefully someone will come along with that solution; but, until then, the above should at least let you continue working. Rick "Gus Chuch" wrote in message ... No the form is already shown. I show the form in the workbook open sub. i just would like to have the textbox get focus again when I click on a new cell. -- thank You "sebastienm" wrote: Hi , Do you mean you want the form to show? Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.Show End Sub -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Gus Chuch" wrote: How would you set focus back to a form once a new cell has been clicked ? Iv tried the following but no luck Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.TextBox1.SetFocus End Sub Any ideas? -- thank You gus |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
set focus to form
And then, using the same idea from my last post, we can eliminate the entire
If/Else/Then block altogether... Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) UserForm1.Show SendKeys "{TAB}+{TAB " & 1 + UserForm1.ActiveControl.TabIndex & "}", True End Sub Rick "Rick Rothstein (MVP - VB)" wrote in message ... Not that it is all that critical, but we can condense the two statements in the Else block down to a single statement... Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) UserForm1.Show If UserForm1.ActiveControl.TabIndex < UserForm1.TextBox1.TabIndex Then SendKeys "+{TAB " & UserForm1.ActiveControl.TabIndex & "}", True Else SendKeys "{TAB}+{TAB}", True End If End Sub Rick "Rick Rothstein (MVP - VB)" wrote in message ... Okay, this is a kludge, I mean a **real** kludge, but it does seem to work. Copy/paste this event code into the code window for the WorkBook.... Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) UserForm1.Show If UserForm1.ActiveControl.TabIndex < UserForm1.TextBox1.TabIndex Then SendKeys "+{TAB " & UserForm1.ActiveControl.TabIndex & "}", True Else SendKeys "{TAB}", True SendKeys "+{TAB}", True End If End Sub I'm sure there must be a "cleaner" way to do this, and hopefully someone will come along with that solution; but, until then, the above should at least let you continue working. Rick "Gus Chuch" wrote in message ... No the form is already shown. I show the form in the workbook open sub. i just would like to have the textbox get focus again when I click on a new cell. -- thank You "sebastienm" wrote: Hi , Do you mean you want the form to show? Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.Show End Sub -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Gus Chuch" wrote: How would you set focus back to a form once a new cell has been clicked ? Iv tried the following but no luck Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.TextBox1.SetFocus End Sub Any ideas? -- thank You gus |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
set focus to form
no that did'nt work. But I did figure out that if I do a SendKeys ("%{F6}") I
get the UserForm to be activate again. But I still cant bring focus back to my textbox1 without clicking on it. -- thank You "Rick Rothstein (MVP - VB)" wrote: Okay, this is a kludge, I mean a **real** kludge, but it does seem to work. Copy/paste this event code into the code window for the WorkBook.... Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) UserForm1.Show If UserForm1.ActiveControl.TabIndex < UserForm1.TextBox1.TabIndex Then SendKeys "+{TAB " & UserForm1.ActiveControl.TabIndex & "}", True Else SendKeys "{TAB}", True SendKeys "+{TAB}", True End If End Sub I'm sure there must be a "cleaner" way to do this, and hopefully someone will come along with that solution; but, until then, the above should at least let you continue working. Rick "Gus Chuch" wrote in message ... No the form is already shown. I show the form in the workbook open sub. i just would like to have the textbox get focus again when I click on a new cell. -- thank You "sebastienm" wrote: Hi , Do you mean you want the form to show? Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.Show End Sub -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Gus Chuch" wrote: How would you set focus back to a form once a new cell has been clicked ? Iv tried the following but no luck Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.TextBox1.SetFocus End Sub Any ideas? -- thank You gus |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
set focus to form
Did you put the code I posted in the Workbook (not the Worksheet) code
window? The Title for the window will say... Book1 - ThisWorkbook (Code) although the number attached to the word Book could be a different number. To get to this window, double click the ThisWorkbook entry in the Project Explorer listing on the left. By the way, I used the Workbook module because that way the functionality would be available from any worksheet in the workbook. Oh, and make sure you use the much shorter last code that I posted. Well, I just posted it (there was a correction), and it is short enough, so I'll save you the trouble of locating it; here it is... Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) UserForm1.Show SendKeys "{TAB " & 1 + UserForm1.TextBox1.TabIndex & "}+{TAB " & _ 1 + UserForm1.ActiveControl.TabIndex & "}", True End Sub Rick "Gus Chuch" wrote in message ... no that did'nt work. But I did figure out that if I do a SendKeys ("%{F6}") I get the UserForm to be activate again. But I still cant bring focus back to my textbox1 without clicking on it. -- thank You "Rick Rothstein (MVP - VB)" wrote: Okay, this is a kludge, I mean a **real** kludge, but it does seem to work. Copy/paste this event code into the code window for the WorkBook.... Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) UserForm1.Show If UserForm1.ActiveControl.TabIndex < UserForm1.TextBox1.TabIndex Then SendKeys "+{TAB " & UserForm1.ActiveControl.TabIndex & "}", True Else SendKeys "{TAB}", True SendKeys "+{TAB}", True End If End Sub I'm sure there must be a "cleaner" way to do this, and hopefully someone will come along with that solution; but, until then, the above should at least let you continue working. Rick "Gus Chuch" wrote in message ... No the form is already shown. I show the form in the workbook open sub. i just would like to have the textbox get focus again when I click on a new cell. -- thank You "sebastienm" wrote: Hi , Do you mean you want the form to show? Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.Show End Sub -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Gus Chuch" wrote: How would you set focus back to a form once a new cell has been clicked ? Iv tried the following but no luck Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.TextBox1.SetFocus End Sub Any ideas? -- thank You gus |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
set focus to form
ok I see.
Seems like the behavior (or lack of) only happens when the control is the first one on the userform. That is, I added a non-visible TextBoxDummy, and in the userform _Activate event, i do: Private Sub UserForm_Activate() TextBoxDummy.Visible = True TextBoxDummy.SetFocus TextBox1.SetFocus TextBoxDummy.Visible = False End Sub Seems to be working. -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Gus Chuch" wrote: No the form is already shown. I show the form in the workbook open sub. i just would like to have the textbox get focus again when I click on a new cell. -- thank You "sebastienm" wrote: Hi , Do you mean you want the form to show? Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.Show End Sub -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Gus Chuch" wrote: How would you set focus back to a form once a new cell has been clicked ? Iv tried the following but no luck Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.TextBox1.SetFocus End Sub Any ideas? -- thank You gus |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
set focus to form
You are getting the UserForm Activate to fire when a **new** cell is
selected??? When I try it, the Activate event for the UserForm does not fire. Rick "sebastienm" wrote in message ... ok I see. Seems like the behavior (or lack of) only happens when the control is the first one on the userform. That is, I added a non-visible TextBoxDummy, and in the userform _Activate event, i do: Private Sub UserForm_Activate() TextBoxDummy.Visible = True TextBoxDummy.SetFocus TextBox1.SetFocus TextBoxDummy.Visible = False End Sub Seems to be working. -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Gus Chuch" wrote: No the form is already shown. I show the form in the workbook open sub. i just would like to have the textbox get focus again when I click on a new cell. -- thank You "sebastienm" wrote: Hi , Do you mean you want the form to show? Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.Show End Sub -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Gus Chuch" wrote: How would you set focus back to a form once a new cell has been clicked ? Iv tried the following but no luck Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.TextBox1.SetFocus End Sub Any ideas? -- thank You gus |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
set focus to form
oops my bad, i thought I had already posted the following code in my 1st post:
Since Gus said the form is already shown when the user click a cell, i assumed it is shown Modeless, am I correct? On _Selection I hide then show the form to generate its _Activate event. ''' ----------------------------------------------------------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.ScreenUpdating = False With UserForm1 .Hide .Show vbModeless End With Application.ScreenUpdating = True End Sub ''' ---------------------------------------------------------------------- -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Rick Rothstein (MVP - VB)" wrote: You are getting the UserForm Activate to fire when a **new** cell is selected??? When I try it, the Activate event for the UserForm does not fire. Rick "sebastienm" wrote in message ... ok I see. Seems like the behavior (or lack of) only happens when the control is the first one on the userform. That is, I added a non-visible TextBoxDummy, and in the userform _Activate event, i do: Private Sub UserForm_Activate() TextBoxDummy.Visible = True TextBoxDummy.SetFocus TextBox1.SetFocus TextBoxDummy.Visible = False End Sub Seems to be working. -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Gus Chuch" wrote: No the form is already shown. I show the form in the workbook open sub. i just would like to have the textbox get focus again when I click on a new cell. -- thank You "sebastienm" wrote: Hi , Do you mean you want the form to show? Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.Show End Sub -- Regards, Sébastien <http://www.ondemandanalysis.com <http://www.ready-reports.com "Gus Chuch" wrote: How would you set focus back to a form once a new cell has been clicked ? Iv tried the following but no luck Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.TextBox1.SetFocus End Sub Any ideas? -- thank You gus |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Form focus | Excel Programming | |||
| Focus in a form | Excel Programming | |||
| set focus on user form | Excel Programming | |||
| User Form focus | Excel Programming | |||
| Sending Form Focus? | Excel Programming | |||
Hybrid Mode
