Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 638
  • Last Modified:

Excel and VBA

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
futureminds
Asked:
futureminds
  • 3
  • 3
1 Solution
 
calacucciaCommented:
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
 
budgie_bCommented:
Use Excel 2000 and display a non-modal form?

Userform1.show false
0
 
calacucciaCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
budgie_bCommented:
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
 
calacucciaCommented:
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
 
futuremindsAuthor Commented:
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
 
budgie_bCommented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now