VBA pop-ups alternative

Hi Team,
I have been using VBA popups ( using forms to work as popup). But there are some issues like parent window does not work unless untill popup closes.

Is there any way to avoid popups like using Tab or is there any other alternative?

Looking forward to hearing from you.
satmishaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DrTribosConnect With a Mentor Commented:
Ok - I have added some VBA, I think this does what you are after.  Note it will break if you change the sheet names.

You will have to rename the file so that it has the extension ".xlsm"

Sheet 2 is hidden.  Pressing the button unhides  and displays it.

Pressing OK or cancel on sheet 2 will rehide.
workSheet-PopUP--DT.zip
0
 
Martin LissOlder than dirtCommented:
What is the purpose of the popup?
0
 
satmishaAuthor Commented:
Just to get input from user from long list for example there are 50 countries and some values against them.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Martin LissOlder than dirtCommented:
So does the form contain a listbox or similar that contains the countries and values, and the user chooses from them?
0
 
satmishaAuthor Commented:
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.
0
 
DrTribosCommented:
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)

Open in new window

0
 
satmishaAuthor Commented:
Thanks for you prompt reply. But Is there any better way/approach to replace popup at all ?
0
 
DrTribosCommented:
Put it on a ribbon tab?
0
 
DrTribosCommented:
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.
0
 
satmishaAuthor Commented:
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.
0
 
DrTribosCommented:
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.
0
 
satmishaAuthor Commented:
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.
0
 
DrTribosCommented:
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,
0
 
satmishaAuthor Commented:
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
0
 
satmishaAuthor Commented:
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 ?
0
 
Martin LissOlder than dirtCommented:
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.
0
 
satmishaAuthor Commented:
not even two workbooks there is only one workbook with multiple worksheets.
0
 
DrTribosCommented:
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
0
 
satmishaAuthor Commented:
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
0
 
satmishaAuthor Commented:
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
0
 
DrTribosCommented:
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.
0
 
satmishaAuthor Commented:
No prob.. I'll wait...n thanks for replying.
0
 
satmishaAuthor Commented:
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.
0
 
DrTribosCommented:
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.
0
 
satmishaAuthor Commented:
Excellent solution Pal.
0
 
DrTribosCommented:
Glad it helped :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.