Excel and VBA

Posted on 2000-03-05
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 17

Accepted Solution

calacuccia earned 50 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? 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.
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.


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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month10 days, 13 hours left to enroll

628 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