Solved

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

Posted on 2015-01-29
12
80 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
[X]
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
  • 6
  • 5
12 Comments
 
LVL 47

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 47

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
Industry Leaders: 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!

 

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 47

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
 
LVL 47

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 47

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

Technology Partners: 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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 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