Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm mechanics
Have constructed a UserForm - frmDetail - with command buttons relevant to
worksheet Detail. It activates at the worksheet level for the Detail sheet [ie frmDetail.show]. 1. I only want it visible on the Detail sheet. Have tried Unload Me in that sheet's DeActivate procedure, but it doesn't work. 2. When the form is activated, it's focus is on the form itself, so worksheet ops are stalled unless I click somewhere in the worksheet. Prefer the focus to remain on the worksheet until a command button on the form is clicked, then revert the focus immediately back to the worksheet automaticall on that button being clicked. Note - there are no OK buttons, just command buttons that run procedures [ie Application.Run ...]. Thanx in advance for your assistance. Regards, - Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm mechanics
To make the worksheets under the UserForm available, show the UserForm as
non-modal... frmDetail.Show vbModeless You can Show/Hide (or Unload if you prefer) the UserForm in the workbook's SheetActivate event. Put this code in the code window for ThisWorksheet (right click the Excel icon immediately to the left of the File menu item on the menu bar, then select View Code to bring this code window up immediately)... Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name = "Detail" Then frmDetail.Show vbModeless Else frmDetail.Hide End If End Sub -- Rick (MVP - Excel) "MikeF" wrote in message ... Have constructed a UserForm - frmDetail - with command buttons relevant to worksheet Detail. It activates at the worksheet level for the Detail sheet [ie frmDetail.show]. 1. I only want it visible on the Detail sheet. Have tried Unload Me in that sheet's DeActivate procedure, but it doesn't work. 2. When the form is activated, it's focus is on the form itself, so worksheet ops are stalled unless I click somewhere in the worksheet. Prefer the focus to remain on the worksheet until a command button on the form is clicked, then revert the focus immediately back to the worksheet automaticall on that button being clicked. Note - there are no OK buttons, just command buttons that run procedures [ie Application.Run ...]. Thanx in advance for your assistance. Regards, - Mike |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm mechanics
Thanx Rick.
That does work [I had it opening Modeless previously, from the Properties menu]. The keyboard is still inactive when the form comes up, as the focus is on the form. But I can live with that picky detail. "Rick Rothstein" wrote: To make the worksheets under the UserForm available, show the UserForm as non-modal... frmDetail.Show vbModeless You can Show/Hide (or Unload if you prefer) the UserForm in the workbook's SheetActivate event. Put this code in the code window for ThisWorksheet (right click the Excel icon immediately to the left of the File menu item on the menu bar, then select View Code to bring this code window up immediately)... Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name = "Detail" Then frmDetail.Show vbModeless Else frmDetail.Hide End If End Sub -- Rick (MVP - Excel) "MikeF" wrote in message ... Have constructed a UserForm - frmDetail - with command buttons relevant to worksheet Detail. It activates at the worksheet level for the Detail sheet [ie frmDetail.show]. 1. I only want it visible on the Detail sheet. Have tried Unload Me in that sheet's DeActivate procedure, but it doesn't work. 2. When the form is activated, it's focus is on the form itself, so worksheet ops are stalled unless I click somewhere in the worksheet. Prefer the focus to remain on the worksheet until a command button on the form is clicked, then revert the focus immediately back to the worksheet automaticall on that button being clicked. Note - there are no OK buttons, just command buttons that run procedures [ie Application.Run ...]. Thanx in advance for your assistance. Regards, - Mike |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm mechanics
Mike -
Insert AppActivate Application.Caption after the .Show command. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MikeF" wrote in message ... Thanx Rick. That does work [I had it opening Modeless previously, from the Properties menu]. The keyboard is still inactive when the form comes up, as the focus is on the form. But I can live with that picky detail. "Rick Rothstein" wrote: To make the worksheets under the UserForm available, show the UserForm as non-modal... frmDetail.Show vbModeless You can Show/Hide (or Unload if you prefer) the UserForm in the workbook's SheetActivate event. Put this code in the code window for ThisWorksheet (right click the Excel icon immediately to the left of the File menu item on the menu bar, then select View Code to bring this code window up immediately)... Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name = "Detail" Then frmDetail.Show vbModeless Else frmDetail.Hide End If End Sub -- Rick (MVP - Excel) "MikeF" wrote in message ... Have constructed a UserForm - frmDetail - with command buttons relevant to worksheet Detail. It activates at the worksheet level for the Detail sheet [ie frmDetail.show]. 1. I only want it visible on the Detail sheet. Have tried Unload Me in that sheet's DeActivate procedure, but it doesn't work. 2. When the form is activated, it's focus is on the form itself, so worksheet ops are stalled unless I click somewhere in the worksheet. Prefer the focus to remain on the worksheet until a command button on the form is clicked, then revert the focus immediately back to the worksheet automaticall on that button being clicked. Note - there are no OK buttons, just command buttons that run procedures [ie Application.Run ...]. Thanx in advance for your assistance. Regards, - Mike |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm mechanics
Jon,
Works great, thanx. "Jon Peltier" wrote: Mike - Insert AppActivate Application.Caption after the .Show command. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MikeF" wrote in message ... Thanx Rick. That does work [I had it opening Modeless previously, from the Properties menu]. The keyboard is still inactive when the form comes up, as the focus is on the form. But I can live with that picky detail. "Rick Rothstein" wrote: To make the worksheets under the UserForm available, show the UserForm as non-modal... frmDetail.Show vbModeless You can Show/Hide (or Unload if you prefer) the UserForm in the workbook's SheetActivate event. Put this code in the code window for ThisWorksheet (right click the Excel icon immediately to the left of the File menu item on the menu bar, then select View Code to bring this code window up immediately)... Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name = "Detail" Then frmDetail.Show vbModeless Else frmDetail.Hide End If End Sub -- Rick (MVP - Excel) "MikeF" wrote in message ... Have constructed a UserForm - frmDetail - with command buttons relevant to worksheet Detail. It activates at the worksheet level for the Detail sheet [ie frmDetail.show]. 1. I only want it visible on the Detail sheet. Have tried Unload Me in that sheet's DeActivate procedure, but it doesn't work. 2. When the form is activated, it's focus is on the form itself, so worksheet ops are stalled unless I click somewhere in the worksheet. Prefer the focus to remain on the worksheet until a command button on the form is clicked, then revert the focus immediately back to the worksheet automaticall on that button being clicked. Note - there are no OK buttons, just command buttons that run procedures [ie Application.Run ...]. Thanx in advance for your assistance. Regards, - Mike |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm mechanics
Jon,
It's been few weeks since your answer, which worked just fine. Until ... am now working on another computer, and every once in a while I get an error, which Debug says is caused by the appActivate Application.caption in the userForm code. It proves to be true. What I have to do to continue is open a workbook from another directory that has the same userforms [a backup], let it work once or twice, close and reopen Excel, then everything will be fine. ..... For a while. The original computer this never failed on was running Windows Xp, the new one Vista. Although I can't see how that should affect Excel, it's the only difference. The problem is annoying at best, and irritating as it occurs randomly. Any thoughts?? Thanx, - Mike "Jon Peltier" wrote: Mike - Insert AppActivate Application.Caption after the .Show command. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MikeF" wrote in message ... Thanx Rick. That does work [I had it opening Modeless previously, from the Properties menu]. The keyboard is still inactive when the form comes up, as the focus is on the form. But I can live with that picky detail. "Rick Rothstein" wrote: To make the worksheets under the UserForm available, show the UserForm as non-modal... frmDetail.Show vbModeless You can Show/Hide (or Unload if you prefer) the UserForm in the workbook's SheetActivate event. Put this code in the code window for ThisWorksheet (right click the Excel icon immediately to the left of the File menu item on the menu bar, then select View Code to bring this code window up immediately)... Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name = "Detail" Then frmDetail.Show vbModeless Else frmDetail.Hide End If End Sub -- Rick (MVP - Excel) "MikeF" wrote in message ... Have constructed a UserForm - frmDetail - with command buttons relevant to worksheet Detail. It activates at the worksheet level for the Detail sheet [ie frmDetail.show]. 1. I only want it visible on the Detail sheet. Have tried Unload Me in that sheet's DeActivate procedure, but it doesn't work. 2. When the form is activated, it's focus is on the form itself, so worksheet ops are stalled unless I click somewhere in the worksheet. Prefer the focus to remain on the worksheet until a command button on the form is clicked, then revert the focus immediately back to the worksheet automaticall on that button being clicked. Note - there are no OK buttons, just command buttons that run procedures [ie Application.Run ...]. Thanx in advance for your assistance. Regards, - Mike |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm mechanics
I've yet to be graced by Vista, so I can't comment on how it might affect
the operation of Excel. It shouldn't matter, as long as you're not calling into the Windows API. Of course, AppActivate is probably like a Windows API call, wrapped in a VBA command. That doesn't really mean anything, I guess, other than I don't know what's wrong. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MikeF" wrote in message ... Jon, It's been few weeks since your answer, which worked just fine. Until ... am now working on another computer, and every once in a while I get an error, which Debug says is caused by the appActivate Application.caption in the userForm code. It proves to be true. What I have to do to continue is open a workbook from another directory that has the same userforms [a backup], let it work once or twice, close and reopen Excel, then everything will be fine. .... For a while. The original computer this never failed on was running Windows Xp, the new one Vista. Although I can't see how that should affect Excel, it's the only difference. The problem is annoying at best, and irritating as it occurs randomly. Any thoughts?? Thanx, - Mike "Jon Peltier" wrote: Mike - Insert AppActivate Application.Caption after the .Show command. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MikeF" wrote in message ... Thanx Rick. That does work [I had it opening Modeless previously, from the Properties menu]. The keyboard is still inactive when the form comes up, as the focus is on the form. But I can live with that picky detail. "Rick Rothstein" wrote: To make the worksheets under the UserForm available, show the UserForm as non-modal... frmDetail.Show vbModeless You can Show/Hide (or Unload if you prefer) the UserForm in the workbook's SheetActivate event. Put this code in the code window for ThisWorksheet (right click the Excel icon immediately to the left of the File menu item on the menu bar, then select View Code to bring this code window up immediately)... Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name = "Detail" Then frmDetail.Show vbModeless Else frmDetail.Hide End If End Sub -- Rick (MVP - Excel) "MikeF" wrote in message ... Have constructed a UserForm - frmDetail - with command buttons relevant to worksheet Detail. It activates at the worksheet level for the Detail sheet [ie frmDetail.show]. 1. I only want it visible on the Detail sheet. Have tried Unload Me in that sheet's DeActivate procedure, but it doesn't work. 2. When the form is activated, it's focus is on the form itself, so worksheet ops are stalled unless I click somewhere in the worksheet. Prefer the focus to remain on the worksheet until a command button on the form is clicked, then revert the focus immediately back to the worksheet automaticall on that button being clicked. Note - there are no OK buttons, just command buttons that run procedures [ie Application.Run ...]. Thanx in advance for your assistance. Regards, - Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Template for mechanics workorder (parts and labour) | Excel Discussion (Misc queries) | |||
fill userform textbox from userform listbox clik event | Excel Programming | |||
Is there an easy Copy/Paste of a Userform ? (Entire Userform Including tx & cbx's) | Excel Programming | |||
Looping procedure calls userform; how to exit loop (via userform button)? | Excel Programming | |||
Activating userform and filling it with data form row where userform is activate | Excel Programming |