UserForm mechanics

[MikeF]

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

 

[Rick Rothstein]

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

 

[MikeF]

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

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)

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

 

[Jon Peltier]

Mike -

Insert

AppActivate Application.Caption

after the .Show command.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______

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

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)

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

 

[MikeF]

Jon,
Works great, thanx.

Mike -

Insert

AppActivate Application.Caption

after the .Show command.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______

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

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)


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

 

[MikeF]

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

Mike -

Insert

AppActivate Application.Caption

after the .Show command.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______

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

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)


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

 

[Jon Peltier]

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/
_______

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

Mike -

Insert

AppActivate Application.Caption

after the .Show command.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______

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.

:

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)


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