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
  • 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

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

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 …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

832 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