[Webinar] Streamline your web hosting managementRegister Today


Excel and VBA

Posted on 2000-03-05
Medium Priority
Last Modified: 2008-01-09
Newbie with Excel and VBA, and wondering how to be able to "click off" a user form which I make pop up with VBA, and click onto the underlying spreadsheet so I can work with cells for a while then go back to the user form. Any ideas about how to code that?
Question by:futureminds
  • 3
  • 3
LVL 17

Accepted Solution

calacuccia earned 100 total points
ID: 2585879
Hi Futureminds,

Why don't you try following structure

1) Create a command button on the Worksheet (Right-click on menu bar, select 'Forms', drag-and-drop the command button onto the sheet
2) Assign a macro to the command button (normally this will be assigned automatically to Book1!button1_click), than click the 'New' button.
3) Now VBEditor will be opened, showing the sub button1_click. Write this line into this sub (provided you have created a userform, with the name userform1)
Sub Button1_Click()
End Sub
4) Put a command button on your userform, name it as you like (e.g. Switch To Sheet)
5) Right-click on the just created button on your userForm, and select 'View Code'
You'll see the empty macro CommandButton1_Click. Insert the Hide Userform line as beneath

Private Sub CommandButton1_Click()
End Sub

Now when you click that button, you'll go back to the sheet. To show the UserForm again (its values will be the same as before you left it), just click the command button on the worksheet again.

6) Return to main sheet, and test it by clicking on the command button.

Good luck


Expert Comment

ID: 2586893
Use Excel 2000 and display a non-modal form?

Userform1.show false
LVL 17

Expert Comment

ID: 2587079
Budgie B, in what way do you think that will get the main sheet back. What will activate the command. If you're only providing a line with only a part of the problem in a program you're not even sure used by the questioner, I think you have to got GUTS to 'ANSWER' a question, especially as three's already a good comment above.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.


Expert Comment

ID: 2587101
My apologies but a non-modal form will allow a "click off the userform onto the underlying sheet" and any processing can be captured when the userform returns focus.

Apologies again for the vague and hasty answer - it has been withdrawn.
LVL 17

Expert Comment

ID: 2588120
My apologies for the harsh comment, it wasn't meant to sound that way, I was in a hurry when writing it. Normally I'm active in the MS Office area, and over there we don't use the answer option in general, we just provide comments. By the way, non-modal forms are not possible in Excel 97.


Author Comment

ID: 2589625

I had a nasty feeling that the answer you gave would be a possible answer, and I thought it would work even before I asked the question. The reason I asked the question was that I did not think there would be only this 'crude' way of swapping between forms. After all, Excel (and I only have 97 incidentally) is only another window isnt it? And my userform - that's just another another window too isnt it? So wont it let me do what I do in VB, and be able to just click between open windows/forms without the ugly device of the command button? What's even worse is that when you are in the spreadsheet and need to scroll down, you loose sight of the command button as it stays at the top (or wherever you put it).

Your answer's correct so of course I'll credit the points - and thanks for it in any case. But I was hoping that there would be some way of coding the position, such as saying that if the user types 'rhubarb' into the text box on the userform1 and then presses Enter, that he is landed in cell A1. Excel knows the macro is running so why can't it let me capture the next Enter keystroke which the user makes when he has completed the work in cell A1, and have the code take me back to my userform1 following the Enter keystroke.


Expert Comment

ID: 2590904

This could be done to capture when someone leaves a particular cell. You would need to perform the following.

Add a Class Module (Class1)

Public WithEvents App as Application
'This declaration will produce an App variable exposing all the Excel Application events available to trap.
'On the sheet change event you will be able to test which cell & sheet has been changed and then return your focus to the form.

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 If Target.Address = "$A$1" Then
 End If
End Sub

You need to add a bit more code in a BAS module (for an add-in) or WorkBook_Open (for a WorkBook) of the following:

Private Obj As Class1

Sub Auto_Open() 'or WorkBook_Open
'set an instance of Class1.App to the current application so it can 'listen' for events firing
    Set Obj = New Class1
    Set Obj.App = Application
End Sub

Maybe a way out??

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question