Solved

VBA pop-ups alternative

Posted on 2014-04-07
26
198 Views
Last Modified: 2014-04-22
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.
0
Comment
Question by:satmisha
  • 13
  • 10
  • 3
26 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39984572
What is the purpose of the popup?
0
 

Author Comment

by:satmisha
ID: 39984601
Just to get input from user from long list for example there are 50 countries and some values against them.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39984607
So does the form contain a listbox or similar that contains the countries and values, and the user chooses from them?
0
 

Author Comment

by:satmisha
ID: 39984643
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
 
LVL 14

Expert Comment

by:DrTribos
ID: 39984666
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
 

Author Comment

by:satmisha
ID: 39984696
Thanks for you prompt reply. But Is there any better way/approach to replace popup at all ?
0
 
LVL 14

Expert Comment

by:DrTribos
ID: 39984712
Put it on a ribbon tab?
0
 
LVL 14

Expert Comment

by:DrTribos
ID: 39984721
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
 

Author Comment

by:satmisha
ID: 39986977
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
 
LVL 14

Expert Comment

by:DrTribos
ID: 39987517
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
 

Author Comment

by:satmisha
ID: 39990850
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
 
LVL 14

Expert Comment

by:DrTribos
ID: 39990865
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
 

Author Comment

by:satmisha
ID: 39992765
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:satmisha
ID: 39992813
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39992839
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
 

Author Comment

by:satmisha
ID: 39992883
not even two workbooks there is only one workbook with multiple worksheets.
0
 
LVL 14

Expert Comment

by:DrTribos
ID: 39992989
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
 

Author Comment

by:satmisha
ID: 39993092
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
 

Author Comment

by:satmisha
ID: 40000259
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
 
LVL 14

Expert Comment

by:DrTribos
ID: 40000473
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
 

Author Comment

by:satmisha
ID: 40002598
No prob.. I'll wait...n thanks for replying.
0
 
LVL 14

Accepted Solution

by:
DrTribos earned 500 total points
ID: 40005705
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
 

Author Comment

by:satmisha
ID: 40013578
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
 
LVL 14

Expert Comment

by:DrTribos
ID: 40013619
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
 

Author Closing Comment

by:satmisha
ID: 40015902
Excellent solution Pal.
0
 
LVL 14

Expert Comment

by:DrTribos
ID: 40016297
Glad it helped :-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

895 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

17 Experts available now in Live!

Get 1:1 Help Now