Solved

VBA pop-ups alternative

Posted on 2014-04-07
26
194 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 45

Expert Comment

by:Martin Liss
Comment Utility
What is the purpose of the popup?
0
 

Author Comment

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

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for you prompt reply. But Is there any better way/approach to replace popup at all ?
0
 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
Put it on a ribbon tab?
0
 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:satmisha
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
not even two workbooks there is only one workbook with multiple worksheets.
0
 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
No prob.. I'll wait...n thanks for replying.
0
 
LVL 14

Accepted Solution

by:
DrTribos earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Excellent solution Pal.
0
 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
Glad it helped :-)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 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

10 Experts available now in Live!

Get 1:1 Help Now