Is it possible to place a prompt message regarding the time limit or return time when entered time is place on the worksheet? Like "Please be back at 8:30 AM" when the user click the Start time button at 8:00 AM.
View 10 RepliesI am making a break timesheet. My UserForm contains Start and End Time button. User clicks start button, a message prompt will appear containing the time he needs to be back in taking his break. The start time of his break will be pasted on the worksheet.
After that, user needs to click the End button to paste the end time he returned. A message prompt will appear stating the remaining time of his break if he comes before the allotted break time say " You still have 00:15:00 left" click "Yes" to end break click "No" to continue your break." If the user click Yes, the time he click the button will be pasted on the worksheet.
Here is the code I have but I am receiving error message "Method 'Range of object_Global failed":
i have an excel file which tracks break time. The program works by logging in the start time of break and the log out time of the break of every employee. What I would like to do is to have a prompt if the user immediately logs out at an early time. Say if the user's start time break is 1:00 PM, if he happens to log out immediately, he should be given a prompt that he can only log out after 25 of his total 30 minute breaktime. Thus he can only log out at 1:26 PM.
My code below currently logs in and logs out the time but without the prompt i would like to have.
I have some code that checks to see if a file exists on the network. Works great (super fast result) if the file is accessible. Works ok (2-3 seconds) if the file is on the other side of a vpn. Works sloe (5-15 seconds) if the file is there on a vpn network and I'm using a wireless computer (wireless adding extra lag). And finally; really slow because the wifi connection is slow or some what out of range ...... I want my code for checking the existence of this file to stop after a specified amount of time.
Code:
Sub AreWeLive()
On Error GoTo networkprobs[code]......
I have a function which will allow me to search for combination of numbers which will sum to zero. However, because the range could be as long as 300 rows or even the possibility of not finding the combination that will sum to zero, the macro will take ages to complete or even causes the whole excel to hang. So What i want is to instruct macro to stop doing the search if it cannot find the results after say 20 minutes of searching.
Is that possible?
I have a numeric value in G2, (in this case 7).
I have a date value in H2 - in I2 I want the date which is the date in G2 plus the number of days in H2, (so 20/01/2014 + 7 would give 27/01/2014 for example).
BUT - I also want it to account for a scenario where the value in G2 is 'No Expiry', so in that case I want I2 to show 'No End Date'.
A coworker of mine will keep a shared file open for hours at a time, not doing anything to it, just keeping open, meaning no one else can edit it as required. Is there a way to set a time limit for that file to remain open before it saves and closes the file? The file shouldn't need to be open more than 30 minutes at a time for any individual. If it's required to stay open longer for certain instances, is there a way to extend the time as well? I can't find anything even remotely relating to time limits on Excel files. Is there coding or an app I need to apply to accomplish this? Is there an option I haven't seen in the program already?
View 3 Replies View RelatedI often have macros that hide columns. Seems there is a limit to the number of time or columns that can be hidden before you get a debug. Message.
View 9 Replies View RelatedI have a formula in one of my sheets that updates each time a new value is entered...
=INDEX('HR Reports Marco New'!BI:BI,COUNTA('HR Reports Marco New'!BI:BI),1)
The issue I'm having is that Column "BI" has an =SUM formula already in it and the formula above is going to the last value which is 0 but all the way to the end of the year when we are only in July...
Is there a simple function to check if a time has been or is entered in a cell, just like the IsDate function.
View 8 Replies View RelatedI have a textbox on a userform in which I want the users to enter a valid time, such as "1:20" or "0:15". How can I test this input to verify it is a valid time? Also, after this time is entered, how do I subtract it from the current time which I am displaying in another box>
View 6 Replies View RelatedI would like to have the current time/date automatically entered into B1 when A1>0 and I want it to be non volitile, is this possible?
View 9 Replies View RelatedI have a workbook that is accessed through a network by many users. The one problem that occurs with certain users is that they leave the autofilter on when then are in the spreadsheet. It has an autofilter on each title, and the user initials are an option to filter...certain users leave the filter on when they save the sheet so the next time someone else goes in, it only displays the previous users results.
Is there a way to reset the autofilter for the user initials each time the sheet is opened?
I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?
View 2 Replies View RelatedI'm having trouble while trying to use a variable in a cell reference. I have a sheet that is being used to record the time and date of data entered into the workbook. This sheet is divided into columns, each designated to a specific type of data. I have written a macro to enter the current time in the correct column, but that macro is embarrassingly long and complicated because I used a series of if statements to handle all the different data being entered. I'm trying to simplify by using vlookup, and assigning the column value to a variable, and inserting that variable into the cell reference.
Here is the line I need to insert a variable into:
Sheets("Current").Cells(Rows.Count, columnstart).End(xlUp).Offset(1, 0).Value = Now()
Where columnstart is my variable. It is an integer, but it's value will change depending on the type of data being entered.
I have a spreadsheet that requires a formula in column "e". How can I automaticlly extend the formula each time data is entered in column "d" of the next row.
View 7 Replies View Relateddoes anybody knows an idea how to get the time difference without getting the #NUM! error message?
Column A contains Start Time while Column B contains end time, if the time in column A is 11:45 PM and the time in column b is 12:15 AM, then I would get this error message...any idea how can i get the time difference without having to use the [hh]:mm format? i would like to use the hh:mm AM/PM format instead.
I have a vba code that I wrote out to search for a file and update data on a spreadsheet with the new file... however I've come to a point where I'm not sure of what code to use. If I search for a file that is not there I get a runtime error and of course it ask me to end or debug. What I would like for it to do instead is search for the file and if it is not there... display a message box stating that the file searched for is not found and automatically closes the workbook after OK is pressed.
Here is the code for those who may need it....
I have a spreadsheet for payroll. The last column is for net pay. I enter this by hand when I get it back from the payroll tech. There are no formulas in this column. When done entering these figures, I click on "save". Then I close the file. From past experience with this sprdsht, I have found that it doesn't always save this column. It saves all the other info. I have entered in the sprdsht, but not this column. So, I have to re-enter everything in this column and "save" again. Close the sheet, then re-open it to be sure it got saved. Why won't it save this column of info. the first time?
View 4 Replies View RelatedVBA macro to change the color of a cell automatically for a specific period of time -say 5 minutes, based on the value the subject cell holds at that time. The cell value is not manually entered but comes from a sub.
There will be hundreds of such cells so that the macro must be able to be repeated for other cells utilizing their individual cell values as well.
I have a worksheet with checkboxes in each cell in A11:A110 as well as O11:O110. When a checked, the current time appears in the corresponding cell, using this code for example, A11 :
Private Sub CheckBox1_Click()
*
*** Range("A11").Select
*** ActiveCell.FormulaR1C1 = "=NOW()"
*** Range("A11").Select
*** Selection.Copy
*** Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
******* False, Transpose:=False
*** Range("A11").Select
*** Application.CutCopyMode = False
End Sub
This system is used to track time elapsed. Example, the first event of the day begins, so I click the checkbox in A11 and the time appears in A11. When the event ends, I click the checkbox in O11 and the time appears in O11.
Now what I would like to modify is the following:
After clicking A11, if 30 minutes have elapsed and I still have not checked the end time in O11, I would like the whole row to highlight in red, a pop up message box should advise me that 30 minutes have elapsed. I could click OK and remove the message box. The whole row remains in red highlight until I finally check the checkbox in O11.
Obviously I'd copy the code for each row.
I assume the code goes with the checkbox's code for A11? Also, is it possible for a cell to flash? (highlighted white, then red, then white, red, etc...)
Every time I run it I get a Run Time Error Message 1004 when trying to custome sort on D4.
Rng2.Select
With Selection
.Validation.Delete
.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlYes
.Sort Key1:=Range("D4"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:="L,M,H,Contract", DataOption:=xlSortNormal
End With
What is the best option to validate a value entered through the below prompt?
View 4 Replies View RelatedVB:
Sub exitbox()
MsgBox "This will close in 20 seconds"
If Application.Wait(Now + TimeValue("00:00:20")) Then [code]...
I am trying to close this msgbox 20 seconds from now, but I cannot get it working.
I want the users to store time only in some of the textboxes in a form so I have used control tip text for those textboxes. But i want to write vba code for displaying the error message if the users don't enter time in hh:mm:ss in those textboxes.
View 7 Replies View RelatedI am working on a time table worksheet and I need to insert a time Offset that can range from 5 minutes to an hour with 5 minute increments. The message box or user form will prompt, "How long is your Offset:"
The problem is that I only know how to do make a drop down list that is populated from cells on a sheet. Do I have to use a user form?
I open a spreadsheet or workbook that has links to other workbooks I am notified by the message bar that content has been blocked. I click Options > Enable this content > OK. Even if I don't make any changes I'm prompted to save changes when I close the file. If I hit Yes or if I hit No, I will still be prompted to enable content in the message bar the next time I open the same file.
I have changed the External Content settings in the Trust Center to "Enable all data connections (not recommended)" and "Enable automatic update for all workbook links (not recommended)" but I still get the messages.
My Message Bar is set to "Show the message bar in all applications when content is blocked" because I read on Microsoft KB that the other option of "never show information about blocked content" will still block the content, just not tell you about it, which doesn't fix my issue.
The files I'm opening and the files linked to and from them are all on the same shared drive on a file server in the office. Is there a way to tell Office '07 that our file drive is a trusted source by default?
I am currently using the below to generate a message prompt. I have two questions that require assistance.
1. How can I furthur tweak it to show the value contains in the cell that correspond to the date?
For example in cell A2 contains date value; Cell B2 is the order number.
If the date in A2 matches today's date, the message prompt should carry Cell B2's value instead of B2 that is displayed as of below code.
2. Can I set the prompt date to be like 15 days after today's date?
Example: Today is 05/01. There will be a prompt if the date in cell A2 is 05/16.
Private Sub Workbook_Open()
Dim r As Range, ff As String, msg As String
With Sheets("Test")
Set r = .Columns(1).Find(Date, , xlFormulas, xlWhole)
If Not r Is Nothing Then
ff = r.Address
Do
msg = msg & vbLf & r.Address(0, 0)
Set r = .Columns(1).FindNext(r)
Loop Until ff = r.Address
End If
End With
MsgBox IIf(Len(msg), "Matches Found:" & msg, "No match found")
End Sub
trying to limit the number of characters entered in a cell.
I clicked on Custom then =LEN(A10)=6 which works when i enter more or less than 6 characters, however it lets you paste in something which has more than 6 charters.
I have a sheet that has vast amount of rows and all I need is for a message prompt to appear if there is an empty cell in columns A to E and if possible the blank cells reference number to be displayed in the message prompt?
View 9 Replies View Related