How to switch from userform to shhet and back

Hello

I have a userform into which i would like to select US States from a USA map by clicking on the state.

I have a sheet with the USA map, with code that accepts the click on a state and display its Name.


I do not know how to integrate the userform and the Map.

I would like to:
1 - have a button on the userform that will display the sheet with the map.
2 - click the state on the sheet,
3 - re-display the userform with the state name I clicked on

My missing part is:
How to switch from my userform to the sheet, and after the click on the state, re-display the userform

thanks for any idea

Is there any chance you could post your workbook? It's a little hard to follow what you're trying to do without it.

Sure

I created a very tiny sample to show what I need.

when you open the file the userform will be displayed.

click on the MAP button and Sheet1 with the USA map will be displayed - (behind the userform)


hit cancel to remove the userform.


now - try Sheet1 first to understand what I need.

select Sheet1
click on any state
you will see a msgbox with the state name


I am looking for a way, to display the userform ->> click the MAP button --> select a state from Sheet1 --> see its value in the userform textbox.

then click again on the MAP button -> select another state from Sheet1 --> append its value to the userform textbox.


I hope I am clear.

to summarize - display a userform, selected few states using Sheet1, and collect all the selected states into the userform textbox.


Many thanks for help !!!!!!!!!!!!!!!!!!!!!!!!

How's the attached do? It's not particularly neat, but I think it's doing what you want.

You'd be much better off using a listbox rather than a textbox on the form.

Thanks !!

getting very close.

1 - I can hardly see the code changes you added.... can you describe the changes you applied ?

2 - In order to click on the state, I need to click on the userform "cancel" button - can this be avoided ?


I mean that the natural way is to click "MAP" and then click select state and another select state etc..

maybe there is a need to add a "CLOSE" button to Sheet1 that will 'close' sheet1 and redisplay the userform with all the states that were selected

Is this possible ?


thanks again !!!!!!!!!!!!!!!!!!

OK, a slightly different version is attached.

Changes are:

1. The auto load macro no longer displays the form

2. Instead of a message box appearing when you click on a state the description is added to the text box on the form instead.

3. I've added a button to show the form, rather than have it appear every time you click a state.

Are we there?

Oh YEAH

We are 99% there


Many thanks.


Now I will try to integrate it into my application

Will not hesitate to post another question if I will have more problems.


thank you !!!!!!!!!!!!!!!!!!!!!!!!!!

I am back quicker than I thought....

the starting point of the tiny sample should remain as it was in the beginning - means - the auto_open displays the userform.

reason is that in my application - I open the userform, and from there I need to go to the States Sheet - do select/select/select and at the end - close Sheet1 and re-display the userform with the selected states.

In the latest file you sent me, we start from Sheet1 - and after doing select/select - the userform is displayed - so actually - the switching from userform to Sheet1 and back was not resolved...

So I am still with my biggest problem which is - how to display the userform / click the map button - select / select / select on Sheet1 - close Sheet1 and re-display the userform with the selected states...


Any idea ?


thanks !!!

Just change the auto open macro back to saying userform1.show, then? Is that right?

not so...

after changing it, the userform is displayed, and click on the MAP button displays Sheet1 behind the userform. So still I need to click Cancel in order to click on a state...

Is there a way to hide the userform after I click on the map button ?


thanks !!

not so...

after changing it, the userform is displayed, and click on the MAP button displays Sheet1 behind the userform. So still I need to click Cancel in order to click on a state...

Is there a way to hide the userform after I click on the map button ?


thanks !!

Yes, change the code behind the map button from:

Private Sub CommandButton1_Click()
Sheets("Sheet1").Select
End Sub

To:

Private Sub CommandButton1_Click()
Sheets("Sheet1").Select
Unload Me
End Sub

nope... still the userform stuck there...

Sorry, that's because your form has code in to stop it being unloaded. Instead of "Unload Me" use "Me.Hide".

p e r f e c t !!!


now I see how to integrate it in my code.


Thank you !!!!!!!!!!!!!!!!!!!!!!!!!

Don't forget to mark this thread as solved, if you're happy your question has been answered.

almost there...

My application is XLA so when I run it - I have actually two projects.


The Sheet1 (with the MAP) is on different project than the userform.

My question is:

The close button - on Sheet1 is used to redisplay the userform.

Any idea how do I "show" a userform that belongs to a different project ?

I looked around for the syntax but none...


thanks for help !!!!