Solved

Excel and VBA

Posted on 2000-03-05
7
607 Views
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?
0
Comment
Question by:futureminds
  • 3
  • 3
7 Comments
 
LVL 17

Accepted Solution

by:
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()
Userform1.Show
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()
UserForm1.Hide
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

Calacuccia
0
 

Expert Comment

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

Userform1.show false
0
 
LVL 17

Expert Comment

by:calacuccia
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.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Expert Comment

by:budgie_b
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.
0
 
LVL 17

Expert Comment

by:calacuccia
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.

Calacuccia
0
 

Author Comment

by:futureminds
ID: 2589625
Calacuccia:

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.

Thanks.
0
 

Expert Comment

by:budgie_b
ID: 2590904
Futureminds

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
    UserForm1.Show
 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??
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now