Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Excel User Form and Active Excel Work Sheets Question

  Asked By: Diana    Date: Oct 01    Category: MS Office    Views: 4244
  

Dose anyone know if it is possible in Excel to automaticaly show an
Excel User Form upon activating an Excel Work Sheet?

Also I was wondering if it was possible to have an Excel User Form
Shown while the user is editing information on the Active Work Sheet.

Any ideas would be greatly appriciated.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Aidyn Smith     Answered On: Oct 01

: Dose anyone know if it is possible in Excel to automaticaly  show
: an Excel user  Form upon activating  an Excel work  Sheet?

Yes. Like Windows, VBA is (can be) event driven. To view the
available workbook events:

Create a new workbook.
Open the VBA editor (Alt + F11).

In the Project Explorer (Ctrl + R), look for the Microsoft Excel
Objects folder.
Open it if it is closed.

Right-click on the ThisWorkbook object and select View Code.
Under the (General) Dropdown menu in the code editor, select
Workbook.
To the right is another dropdown menu with all the events for
workbooks.
Select the SheetActivate event.

Create a userform named UserForm1 and open the
Workbook_SheetActivate sub routine.
Add "UserForm1.Show" as below.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
UserForm1.Show
End Sub

Go to the workbook (Shift + F7). Activate a sheet  by selecting
its tab.
The UserForm should appear.


You can also use the Object Browser (F2) to view object
events. Events have a yellow lightening bolt icon next to
their names.


: Also I was wondering if it was possible to have an Excel User
form  Shown while the user is editing  information on the Active
: Work Sheet.

No. You could show  a userform when a cell is selected (before
the edit) or when a sheet change occurs (after the edit). I guess
you could show the form on a keypress, but that may be
problematic as many keys are used for navigation, not editing.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
UserForm1.Show
End Sub

 
Didn't find what you were looking for? Find more on Excel User Form and Active Excel Work Sheets Question Or get search suggestion and latest updates.




Tagged: