Help with pop-up dialog box in Excel

Posted on 2011-02-24
Last Modified: 2012-05-11
With some help of some of the members of this site the Excel tool attached to this posting is almost ready I need assistance with the last bit.  

The tool basically compares two sets of data and creates a report showing the changes between “Prior Data” and the “Current Data”.  To run the report the user pushes the “Process Changes” button on the top right corner of the “Data” worksheet.  The report is created on the worksheet “Changes”.

The part I need help with is after the “Changes” worksheet is created I want to – as much as is practical – force the user to supply “justification” for the changes shown on the “Changes” worksheet.  I hope to accomplish this by having a dialog box pop-up for the user to enter text for their justification.  I would like this pop-up dialog to appear once for every row appearing on the “Changes” worksheet (excluding the top two header rows).  I’ve never worked with pop-up dialog boxes before and don’t really know how to approach a solution.
Finally I would like the text the user enters (not more than 50 characters) into the pop-up dialog box to appear in column P on the Changes worksheet.  

Any help would be much appreciated.

Question by:dec789
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 24

Expert Comment

ID: 34970750
Try this:
Sub RequireExplanation()

    Dim i As Long
    Dim lastRow As Long
    lastRow = Worksheets("Changes").Range("A" & Rows.Count).End(xlUp).Row

    For i = 3 To lastRow
        Cells(i, "P").Value = Left(InputBox("Enter Change Justification for ID " & Cells(i, 1).Value & ":", "Change Justification"), 50)
    Next i

End Sub

Open in new window


Author Comment

ID: 34971011
That works great!  I was envisioning tons of code to pull this off.  Thanks for the solution can I ask for one tweak???  Can you put a check-box on the dialog box that if checked the justification supplied would be applied to all rows of the Changes report?   If you don’t have time I understand you have already more than earned the 500 points.
LVL 24

Accepted Solution

broomee9 earned 500 total points
ID: 34971166
>>Can you put a check-box on the dialog box that if checked the justification supplied would be applied to all rows of the Changes report?

The "pop up box" is an input box, and you can't add any controls to it, like a checkbox.  However, I can add a message box after it asking if you want to apply this reason to all changes.  See attached.

Author Closing Comment

ID: 34971222
Thank you broomee9 that was an excellent solution.  Thank you again for all you help.
LVL 24

Expert Comment

ID: 34971253
You're quite welcome :-)

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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