Solved

Picking a row, while a Yes/No popup dialog box is up.

Posted on 2015-01-29
12
70 Views
Last Modified: 2015-02-04
Is there a way to do the following.

Have a Yes/No dialog box popup, that if No is selected.  Exit Sub.  that is the easy part.  Here we are verifying that the user wants to perform this task in case a button is click on by mistake. so if Yes is click that would mean to continue.

Then have another popup dialog box that states.  Before continuing.  Select a row (while the dialog box is up) that has the current date in column B, and then press the Ok or yes button.  If if the date in the selected row is < today, go back and ask them again. (the selected rows date is < today, try again).

If the row select is = to current day, I have a macro to run, however when I do it, I cannot select a row while it is up.
How do I get a popup dialog box to allow the selecting of a row that is = to current date?  We want to force the user to click on a row that has a currect date.
0
Comment
Question by:RWayneH
  • 6
  • 5
12 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40578051
Dialog boxes are modal so you can't do anything while they are open. You could however create a form that looks like the dialog box and show it non-modally.
0
 

Author Comment

by:RWayneH
ID: 40578081
So while a form is up the user can pick a row?

I will need a hand with that if statement... If in whatever row selected is the value in column B = current day?

Do you have a sample file that you could attach?  I have use Yes/No forms before... it is the if statement and get it to identify = current or not, then ask them to try again that has my stumped.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40578100
It would be easier for me if you could dummy up a worksheet with some sample data, but let me ask you this. why are you asking the user to pick the row and then possibly tell him he's wrong, when you could simply use Find in the macro to do it?
0
 

Author Comment

by:RWayneH
ID: 40578135
The sheet is a daily task track sheet that times how long you are on a task.  This process will allow user to restart a given task... starting the timer from where it left off within the same day.  If they pick one that is < today.  The total hours and accumulate tasks for yesterday are set and days total hrs are set.  We cannot add say 2.5 hrs from yesterday into today.  You are going to laugh at the attached file.... this sheet dates back to Excel 4.0 days, it actually uses a dialog box that is on a hidden macro page that is pre-form editor days.  I am slowly working on rewriting this one.  I am having a hard time letting this one go.
 
The process you want to run is the Restart Task button at the top.  It currently is not checking that the restart is within the current day..  Hope this make sense.

Sending days to the Archive sheet tab is not done either.  For some reason when I delete rows, it is screwing up formulas..  that one is next after the Restart button.
TimeTracker.xls
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 40578458
Instead of a MsgBox, consider using an InputBox. This can be configured to allow the selection of a range, which can then be used in your macro.

For example...

    Dim rng As Range
    'turn error handling off to handle the cancel button
    On Error Resume Next
    Set rng = Application.InputBox("Select row that contain current date", "Select Range", Type:=8)
    If rng Is Nothing Then Exit Sub
    If rng.Rows.Count > 1 Then
        MsgBox "Only one row can be selected"
        Exit Sub
    End If
    'set the range to the entire row incase only individual cells is selected
    Set rng = rng.EntireRow
    're-enable error handling
    On Error GoTo 0

Open in new window


More info on Application.InputBox can be found here.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40578508
@RWayneH: Normal macros can be called in code by just using their name. How can I call the Controlled_Add macro via code other then by using the shortcut?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40578580
Try this. In addition to changing the msgbox to a form I removed the ctrl+r assignment from the Controlled_Add macro and added it do a new macro called CheckToday.
Q-28606516.xls
0
 

Author Comment

by:RWayneH
ID: 40580328
Decided to go with the userform.  Now I am stuck at the following.  After clicking yes in the userform.

Private Sub cmdOK_Click()  'When yes is selected

'This is working... user is going to select any cell in a row.
'now how do I check that column B is = to today? (not < today)
        'and the row can not be row 7
'   If it is < today, pop up an idiot msgbox saying something like Try again
'and return to the form again.  (frmRestartTask)


Application.Run "Controled_Add"  'RUNS A EXCEL 4.0 MACRO!!!!
Unload Me
End Sub

Open in new window

0
 

Author Comment

by:RWayneH
ID: 40580351
Ah.. I see  the Ctrl+r is run while the userform is up....   I was trying to make it disappear.

Where do I add these on a True?  I cannot find the If statement for the checking of the day = today


Application.Run "Controled_Add"  'RUNS A EXCEL 4.0 MACRO!!!!
Unload Me
0
 

Author Comment

by:RWayneH
ID: 40580394
and I figured it out...  I posted the corrected below..  had to make sure unrelated other edits.  The last piece is to make this Archive...  I will post again if I need help with that.
TimeTracker.xls
0
 

Author Closing Comment

by:RWayneH
ID: 40590233
Thanks for the help
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40590235
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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 Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

743 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

13 Experts available now in Live!

Get 1:1 Help Now