I have the following code in a simple userform containing only a label:
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Sub UserForm_Activate()
DoEvents
Sleep 4000
Unload Me
End Sub
When I invoke this as a single macro in a blank workbook and immediately change focus by pressing ALT+TAB to enter NotePad the userform is shown and exits normally after 4 seconds.
However, when I issue this code from within a larger macro, and hit ALT+TAB to Notepad, the excel application hangs on the displayed userform until I click back into Excel.
If required, I can happily send you the code as an attachment.
We have one shared excel workbook and it is used by many people (more than 20 simultaneously), is there any way to auto save & close the workbook (session) if a user is inactive for specified time.
I have a protected worksheet. Users wish to be able to track changes in the input cells. The suggested approach for this is to temporarily disable sheet protection and allow them to change the font color, then protect afterwards. What I would like to do is:
i) check whether they are in an input cell ii) if so, then prompt the user with the 'Font Color' dialog box iii) apply the font color selected to the input cell
I'm struggling to find the dialog box I need. I can launch the one to change the interior color, no problem (Application.Dialogs(xlDialogPatterns).Show). But that's no use to me, I just want a color palette that specifically relates to the Font Color
I have got a spreadsheet which is being used as a database. I would like the spreadsheet to close automatically if the user has not "touched" it for a specific amount of time (say 1hr).
2 scenarios:
a/ The user is working in Excel but with another workbook. b/ The user has the excel spreadsheet open but is working with another application, say Word
I experimented with Workbook_WindowDeactivate but I do not know how to stop the process if the Workbook is reactivated (Maybe a DO ...LOOP calling a function returning a True/False statement on the event Workbook_WindowActivate !!). My way of thinking is the following
Workbook is deactivated Start a timer If the workbook is not reactivated with 1hr, save and close (no user intervention wanted) Else stop timer Repeat process
I have thousands of timestamps that have a start & end date and time in 2 separate columns. (one named start and one named end...)
I also have numerous set time periods that i'm interested in.. (about a dozen or so)
for example 01/01/2008 - 05/01/2008, 07:30:00 - 10:00:00
What i need is to be able to count the number of times the full time period i am interested (07:30:00 - 10:00:00) in falls in between the thousands of start and end timestamps i have. The time periods must also fall within the date range specifed.
So if my timestamps were Start: 01/01/2008 06:30:00 & End: 02/01/2008 11:00:00, based on the set time period above, there would be a count of 2
and if my timestamps were Start: 01/01/2008 07:05:00 & End: 02/01/2008 09:00:00 there would be a count of zero as there is not a full uninterupted timeperiod 07:00:00 - 10:00:00 between these timestamps.
and if my timestamps were: Start 01/01/2007 07:00:00 & End 02/01/2007 10:00:00 the count would be zer as this is a year early!
I want to return a value based on once off time related data which is captured at 4 time periods throughout a day. I have another set of data which does not line up with this data but I want it to return the value closest earliest value.
Given this data: 27-6-14 3:00 12 27-6-14 7:00 18 27-6-14 11:00 19
If I have a time like 27-6-14 5:00 I want to return a value of 12 (Previous time). How would this be possible?
Calculate certain time increments for various work-shifts. I have a start time,finish time and increments of time across the spectrum of 24 hours. There are also multiple start time across the 24 hour period with some start times begining on one day and ending on the next day.
Example
In B5 Startime is 22:00 In C5 Finishtime is 06:30
In I3 increment begins at 00:00 In I4 increment ends at 00:30
The employee working the shift from 22:00 - 06:30 would fall into the time increment of 00:00 - 00:30 where another employee working a different shift (08:30 - 17:00) would not. I'm looking for a formula that would return a 1 in a cell if the employee fell into the 00:00 - 00:30 time increment and a 0 in a cell in the employee did not fall into the time increment.
I am trying to determine a formula to know a period time for example i started to test a unit at 10:20 am of 10/02/2011 and i would like to add 52 hours of test and i would like to know when and at what time will finish the test?
I have a project where I have to work out the value of cars over a period of time.
The cars depreciate at 36.9% per year over a 5 year period and I can't work out for the life of me how to create a formula in excel that give me the values at the end of each year.
Each car costs £10000 and I need to know what the value of each car is at the end of each year
I have sheet that is populated with data daily. I wish to create graphs from information contained within a table in this sheet where i can select the start date and end date of the graph possibly from a drop down menu. I will be creating multiple graphs and wish for them all to change when i select the time period.
I've searched a few of the posts here about freebies in terms of adding a trial period to your spreadsheet. I'm just starting to get into VBA programming (beginnner), but I was wondering if anyone here could suggest actual programs or add-ins that I could buy that would allow me to create a trial version of my spreadsheet? either by allowing the user to use it for a few days or to allow the user to use it for a specifed # of times.
I'm willing to pay for this, but I can't afford those expensive Licensing/Copy protection programs....I need a simple solution in between $20 and $60....
What I would like to do is to be able to open a second workbook from a user form, preform some work on it then save and come back to the same place in the original user form.
So in steps:
1 : user clicks command button to open user form 2: user then clicks on command button on userform that opens 2nd workbook via a yes / no message box, but closes userform on 1st workbook (would be ideal if this could stay open, but hidden) 3: user then does work on 2nd workbook, 4: userform on 2nd workbook saves then activates the 1st workbook and reopens the userform
This is where no matter what I try I cant get the command button on the 1st userform to be clicked automatically so the yes / no message box appears.
This is part of the code in the 2nd sheet commandbutton that saves / closes / opens
Code: Unload Me ActiveWorkbook.save Windows("ABC.xlsm").Activate Sheets("Request Sheet").Activate Call Sheets("Request Sheet").ForceClickOnBouttonXYZ Call UserForm1.CommandButton6_Click 'this is where I cant get it to work!! Windows("xyz.xlsm").Close ABC is the 1st workbook xyz is the 2nd workbook
This is the code on the 1st workbook I use to call on the 2nd workbook
Code: Public Sub ForceClickOnBouttonXYZ() Call CommandButton1_Click End Sub
I want to assign a macro to a button that will unhide a tab for about 10 seconds, then rehide it and navigate to another tab. How do you do timed commands?
I have a in field column M of my table that gives the week number of various dates within the table.
I would like to find the high value reached during each week based on the Highs which are located in column P, the highs consist if workdays during each week number.
I would like this to appear as a new calculated field entitled "Week High", I am assuming I need to input a custom formula to do this. I also want to create calculated fields for the the high reached during each year, the year is in column L
I have an Access database, with a table of jobs processed.
One column for Job ID (Unique), Date it was processed, By Who it was processed and Comments ascoiated.
I need to chart a graph of Jobs over a period of time. How many jobs done per date. In excel preferebly, I have programs like Database Plus for excel. And I am familiar with VBA. I can't figure out how to uniquely sort how many jobs for each date. As there are numerous job entries for on the same dates. I think it can be done with an SQL query or sorting it with VBA somehow. Any help will be greatly appreciated. Or can anyone suggest an application designed for charting with Access databases in Excel?
compare two lists of data in order to identify the possible matches considering date&time and the location stored in different columns as shown in the example file attached. The range of date and time for the comparison is one hour, but it can be changed...
I am a flight instructor and legally we cannot work more than 8 hours in any consecutive 24 hour period. I'm trying to create a spreadsheet that will calculate the totals from that 24 hour period for me.
Right now I have something that sort of works, but not the way I would like it. I have a column for "time" that has the date and ending time of the flight and I am using that as if the total flight occurred at one moment (the ending time).
So, for instance let's say I did these flights
1: 8AM-10AM (2 hours, clocked at 10AM) 2: 11AM - 2 PM (2 hours, clocked at 2PM) 3: 7AM-9AM the following day (2 hours, clocked at 9AM)
If I have my formula calculate the time for 9AM the following day (totaling the past 24 hours) the first flight won't be included in the calculation since the hours from that flight is only imputed at 10AM. The formula would read 4 hours rather than the (actual) 5 hours.
Here is my workbook : 8 hour calculator sample.xlsx
Here are the formulas I am using
The time is formulated at date and time, with time being the ending flight time
The start time for the calculation is
[Code]....
end time is just =NOW()
Total calculation is
[Code] .....
All I'm trying to do is use the totals under "flight start" and "flight end" instead of the end flight time I had to put in under the date column.
I am creating a timesheet using excel 2003 users enter their shift start/finish time and a break start/finish time. Emplyee's can work night shifts (ie across midnight).
There are penalty rates which apply at different times. I need to be able to work out the amount of worked time that fits into a certain time period. eg. 10pm-7.30am, 7.30am-10pm.
I have a solution based on A clever formula from Daniel Maher that will calculate time within a period. But it doesn't work when the shift goes over two days.
I have attached a spreadsheet to help show the problem .......
I am trying to calculate the average start time for a machine over a period. However the machine start time varies from 10:00 pm to past midnight. I have tried reviewing past posts but cannot seem to find a similar query.
I'm opening a workbook and then running the macro in the workbook. The problem is I would like to build a "timeout" feature if the macro is running too long (as some of the macro's this will run can take days to complete) but I don't know how to run the macro asynchronously. Also I need to know if i can get it to run asynchronous is there an event that will tell me when the process is finished. The calling application of the macro is written in VB6 and opening excel workbooks to run the macros out of. also the "timeout" feature must be done from the VB6 application it cannot be edited into the the workbooks containing the macro.
I have a small range that i need to copy to another sheet for each working day of a time period(01/01/2014-30/04/2014) excluding weekends and holidays, adding the date in the first column of the new sheet.
How to create a formula to determine the longer period of time between two date calculations and enter the longer period of time in the cell.
My Spreadsheet: Cell M2: Birthdate (entered in cell as 11/1/2004) Cell J2: Last date of service (entered in cell as 10/31/2011) Cell G2: Calculation to enter the longer period of time between (M2 (year) + 21) and (J2 (year) + 7)
Example using information from above: 2004 + 21 = 2025 2011 + 7 = 2018 2025 is the result I want added to G2
If there is any way to include the month/year in G2 that would be ideal.
I have a large userform (Main) that the users enter data into. At the end of the data entry the users click OK and the data is transferred to a spreadsheet. I then ask the users if they want to add another record. If they click "Yes" all is OK. If they click "No" the userform should close and another "switchboard" type userform should open.
My problem is that try as I might I cannot get the (Main) userform to close. It only does so after the switchboard closes.
If I don't load the switchboard, main closes without issue.
I have left out the bulk of the data transferred to the "data" sheet in the code below.
Private Sub Main_OK_Button_Click()
Application.ScreenUpdating = False
Sheets("Data").Visible = True
Sheets("Data"). Range("F2").Value = Surname.Text
Dim result As VbMsgBoxResult result = MsgBox("Do you wish to enter another record?", vbYesNo + vbInformation, "Continue...") If result = vbNo Then
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?
I am trying to make a line graph showing the gradual rises and falls in profit over a period of time, when I use the data and click line graph normally, it will plot each bit of data individually rather than gradually, for example;
If I tried to plot the above data, the graph will not show a gradual rise but instead will show a value of £1604.88 on the 23rd of September when instead I would want the chart line to be at £29,287.55 (The total).
I need to determine a formula which will allow me to calculate a future date based upon a current date with varying time periods.
For example:
I have a bill which is paid on the 15th and last business day of each month. I would like to be able to see the next due date regardless of what day of the week it is.
I have a bill which is paid every other Tuesday. I would like to know the next due date without having to enter +14 for every due date in the future. In other words, it is preferable to be able to open the spreadsheet and automatically see the next due date, not use autofill to repeatedly add +14 to a previous date which would limit the # of future due dates that could be calculated.
I have a bill which is paid on the last business day of each month, not the last Friday of each month. I would need excel to return a value for the last day of the month which = Monday-Friday, regardless of what day of the week it may be as long as it isn't Saturday or Sunday(holiday exclusion would be nice but not required).
I'm working on a dynamic payroll spreadsheet that will automatically calculate the overtime worked in a week. Right now, I'm running into a snag. My issue is with the formula in Column R. Right now, as shown below, it is doing the calculation based on regular hours minus 40 to determine the OT time. The snag is very messy and it lay in this: while the row by row calculations for total overtime worked for the week is correct, the sum at the bottom is very much off. I need an accurate method to sum the hours of overtime for the given column.
Here are the guidelines for the pay periods and overtime:
1. The pay periods for the month go from the 1st to the 15th and the 16th to EOM (End of Month). This means that the pay period could end on any given day of the week. More on this in a moment.
2. A work week is defined as Sunday to Saturday.
3. Overtime is calculated based on the rule of anything over 40 hours in a given work week.
4. Holiday hours worked do not count towards the 40 hour mark in granting overtime since Holiday pay is automatically overtime.
If it were just a matter of a bi-weekly (every 2 weeks) pay period, I would simply state =IF(weekday(DATE)=7,Hours_Worked - 40,0), and tag a SUM(range) at the bottom. Unfortunately, with it being a semi-monthly (twice a month), the end of the pay period could be a Wednesday, so a reference to day of the week won't work unless the formula can dynamically determine which set of data to evaluate.
I'm completely willing to toss out the current method of determining overtime. This is the calculations sheet that references a cleanly formatted and designed time card on a tab called "Time Card", so this isn't the full workbook. In fact, once the whole thing is done, this calculation sheet will be hidden.
Columns M and N (which are formula referenced in Column P) are basic End - Start calculations and were hidden to simplify the display as well as the number of formulas displayed.
Column L (formula referenced) is a Yes/No display for if the date in question is holiday pay.
