VBA pop-ups alternative

What is the purpose of the popup?

Just to get input from user from long list for example there are 50 countries and some values against them.

So does the form contain a listbox or similar that contains the countries and values, and the user chooses from them?

Simply contains counties and values with OK and cancel button. User selects and click on OK button so that values comes to parent one.
Here One way is simply use the popup functionality and other way is to use Worksheet  Tab(In this tab I define the Counties and value with OK and cancel button) and do hide and unhide.
Please suggest which approach is better or feel free to let me know if there is anything better then above as well.
Looking forward to hearing from you.

I think the specific issue you mentioned, parent window not working if popup is open, can be overcome by opening the userform as "modeless"...

From VBA Help:

When a UserForm is modal, the user must supply information or close the UserForm before using any other part of the application. No subsequent code is executed until the UserForm is hidden or unloaded. Although other forms in the application are disabled when a UserForm is displayed, other applications are not.

When the UserForm is modeless, the user can view other forms or windows without closing the UserForm.

Modeless forms do not appear in the task bar and are not in the window tab order.

frmMyForm.Show (False)

Select all Open in new window

Thanks for you prompt reply. But Is there any better way/approach to replace popup at all ?

Put it on a ribbon tab?

You could have a gallery on a ribbon and the gallery could be populated from the data in the "hide/unhide" sheet you referred to... the user would simply click a gallery item and that would fire a macro (passing an index value to same).  

You would need to use XML to do this, you can use the Custom UI editor to edit a template to hold your gallery.

we are not using any ribben control. Apart from that is there any way, I am pretty much satisfy with your answer but getting gredy now :-)

Is using Tab instead of Popup better option ?

Looking forward to hearing from you.

Ok lets both be greedy, you can increase the points and I'll answer as best I can :-)

Whether it is a "better" option would depend on exactly how it is to be used and the way you expect your user to interact with it.  I use pop-up forms when there is a lot of information to collect or when I need to stop the user in their tracks.

On the other hand users go to the ribbon when they feel the need.  One way forward might be to tell your users to go to the ribbon by using a message box if certain criteria have not been met.

surely I am doubling the points Pal.... :-)

I have no option to use ribbon control so lets leave this option.

Not lets come to another option now, I have table of data (In tabular form) from where user has to select multiple values. Now what do use suggest on this. Shall I go and use Popup or make a tab so that when user clicks on popup link I unhide this tab so that user can select multiple values from this. Or is there any other better way other then Ribbon XML ?

Looking forward to hearing from you.

Please confirm - 1
I don't quite understand what you are asking.  Since you do not want to / unable to use a userform or the ribbon I am assuming you are using a plain table in your document.  

Please confirm - 2
What you mean by "tab", I thought you are not using user forms either now...

Perhaps you could put up a picture to make plain what you are referring to.

Cheers,

I am increasing points to 500 now..

Thanks for your reply..

1. Yes, I can not use both user form and ribben xml.  I need sum of value from one column of Table to the parent worksheet1.

2. Tab means worksheet2. I want worksheet2 appears like a popup though it is not popup it is simple worksheet.

Requirement: I have dynamic table like on worksheet2

E.Name      Strength            Percentage(Input field)  
A                      2                          10%
B                      3                          20%
C                      4                          15%
    Total            9                           35%  

This table will come as another worksheet lets say worksheet2  when user click on button on excel worksheet1. User will provide input in third column and click on OK button of worksheet2. Sum of value of second column will go to the worksheet1.

Assuming this would explain in better way.

Looking forward to hearing from you

just to add one more point, Is it possible to make floating worksheet means worksheet comes out like popup. or Is there any better way to achieve this ?

Could the two workbooks (and some or all of their sheets)? If certain people aren't supposed to see certain sheets then you could control that via username/password.

not even two workbooks there is only one workbook with multiple worksheets.

Is it possible to make floating worksheet means worksheet comes out like popup

Ok - I think you are going to hit an end to the possibilities of using a pop-up, from Martins comment of using a 2nd workbook I have the idea of using a different program, for example MS Word.  The thing is that MS-Office provides something to acheive a pop-up effect and for whatever reason you need something different, but I think we are coming close to exhausting the possibilities.

You might explain further why you are unable to use any of the options so we better understand the problem / desire that you have.

That said, if all you really need to achieve is this

I need sum of value from one column of Table to the parent worksheet1.

Then I am not sure why you need a pop-up at all...
Assuming the data to be summed is in Sheet 1 column A then all you need to do is put this in a cell of Sheet 2:

=SUM(Sheet1!A:A)

Hope this helps

Its not the prob of formula.

I have enclosed a sample file. pls hv a look on it.

Here on worksheet1 I hv button to open  worksheet2 and it hides worksheet1(trying to give effect like popup). on worksheet2 user choose various employees through check boxes, in example user selects Employee 'F' and 'J' . sum of the second columns appears as '5' ( of strength of employee 'F' & 'J') when user clicks on 'OK' button I do hide worksheet2 and get value '5' to worksheet1-->'F4' c.ell

Is there any way to make it in cleaner way ( not with Popup and ribben XML).

I hope this might give you better picture.

Looking forward to hearing from you.
workSheet-PopUP.xlsx

Thanks a lot for your valuable input. But appears like purpose is not getting solved. Let me try to explain little bit more.

I want worksheet2 appears like a popup though it is not popup it is simple worksheet.

Requirement: I have dynamic table like on worksheet2

E.Name      Strength            Percentage(Input field)  
A                      2                          10%
B                      3                          20%
C                      4                          15%
    Total            9                           35%  

This table will appears as worksheet2  when user click on button on excel worksheet1. User will provide input in third column and click on OK button of worksheet2. Sum of value of second column will go to the worksheet1-->Cell1.

With the code/sample that you sent I am in doubt whether I could achieve this ? Please suggest.

I hope this gives you better understanding.

Looking forward to hearing from you

I'm out and about for a few days so have been unable to have a proper look.  Will try to get back to you in a few days. Sorry for the delay.

No prob.. I'll wait...n thanks for replying.

Thanks a lot for your reply & apologies for my late reply.

I downloaded the attachment but confused, how to open workbook/worksheet as I could see all * .XML/vlc files.

Please help me how could I open the sheet and which file you are asking to rename ?

I tried to open "\workSheet-PopUP--DT\xl\Worksheet\Sheet1.xml" as XML table but nothing happens. It just opened a table in sheet1.

Am I missing something here ?

Looking forward to hearing from you.

You have to change the extension of the zip file to xlsm not xlm. Office documents are actually a bunch of xml files in a zip container. That is why you see all the xml. To change the extension you might need to change the folder properties so that you can see the extension first.

Excellent solution Pal.

Glad it helped :-)