I scheduled a task to open excel and when it does (at a certain time), a little sub is supposed to run (couple minutes later) via the OnTime Method located in the Workbook Open Event. There is one weird thing though....
1. When Excel opens automatically through Scheduled Task (and you see the clean white sheet WITH gridlines), Sub does NOT run (that is, my file is NOT even opening).
2. When I myself open Excel manually WITHOUT opening a new workbook (in other words, in front of you there will be grey area without gridlines), Sub runs perfectly (in other words, my file opens automatically and Sub performs whatever it is supposed to do).
I have a worksheet that runs a query from an external data program. I have made a macro that refreshes the worksheet and this works ok. Is there a way I can get Scheduled tasks to automatically update this workbook eg overnight. I do not want to leave the workbook open either. Can task scheduler open the workbook, refresh the data, then close the workbook again.
I am looking for a funtion that will help me add the hours someone is scheduled to work. The problem I am having is the format. Here's the format: The days of the week are Mon - Sun (D3-J3). L3 is a total of the hours they are scheduled to work for the week. Cell D3 is 8-2. Cell E3 is 9-3, etc. for the rest of the week. What I am looking to see if possible is in L3 take the row, Take D3 at 6 hours, E3 at 6 hours, etc for the rest of the row, and add them together for the total hours in L3. I have attached a small example file of what I'm trying to do. The format I am looking to use for the hours is as described above.
I have a button that runs a macro every 5 seconds.
I have another button to stop the loop.
Problem happens if a user hits the stop button when the sequence is not running - they get an error.
Here's my code for button 1 (start) and 2 (stop):
Code: Sub Process_Emails_and_Backup_Auto() Application.Run "Process_All_Emails_In_The_Inbox" runtime1 = Now + TimeValue("00:00:05") Application.OnTime runtime1, "Process_Emails_and_Backup_Auto" End Sub Sub StopProcessing() Application.OnTime runtime1, "Process_Emails_and_Backup_Auto", , False End Sub
Is there a way for button 2 - the StopProcessing macro - to run a test like If process is scheduled then run this code - if not then don't?
I've used Excel 2003 to write a macro to analyse some date from our internal proprietary database. I use some Perl to generate a bunch of csv files from the data, and the run the macro to generate graphs and statistics from that. What I would like to do now is run this entire operation as a scheduled task, so that, every week or so, the data is downloaded, the processing is done on the csv files, and the results are saved to a location to be viewed on our internal site.
I'm assuming that the best way to do this would be somehow run something from the command line (to be embedded in a script) which basically says "1) Open this csv file 2) run this macro 3) save the output to this location", but I can't figure out how this can be done.
I have attached a portion of a schedule I have been asked to update to show when more hours are scheduled for a time period than are available on the schedule.
Each Dept.(Man. and DCC) has 16 hours available per day, M-F.
How can I chart or show somehow when a conflict arises? I need to outsource the excess time to another facility in a timely fashion.
Sub ScheduleUpdate() WaitHours = 0 WaitMin = 15 WaitSec = 0 NextTime = Now + TimeSerial(WaitHours, WaitMin, WaitSec) Application.OnTime EarliestTime:=NextTime, Procedure:=LinkUpdates() Application.Run End Sub
Sub LinkUpdates() Sheets("PRICING SHEET").Select Sheets("PRICING SHEET").UpdateLink Name:="P:CommonPRICINGCOMPILATION.xls", Type:= xlExcelLinks End Sub The code does not work at all, just the skeleton to have an idea. I also need help recreating an entirely new macro which preferably works well! The idea is that everyday at 8:00AM this macro starts automatically updating the links every 15 mins (SHOULD NOT MATTER IF THE SHEET IS OPEN OR NOT). But here is the catch. This code should be the code for a checkbox. So when the box is checked, or TRUE, the macro should run normally updating every 15 mins. But when the box is unchecked, the macro should stop (no update occurring). I want this macro because this sheet is linked to several other sheets and it needs constant updating. However, if I need to make a change in the main sheet, no updates should be happening.
Is there any way to find out how many people are scheduled between certain time ranges. For example : I have 5 people scheduled with various start and finish times ie.:
1. 10:00 to 15:00 2. 11:00 to 16:00 3. 08:00 to 14:00 4. 13:00 to 15:00 5. 12:00 to 16:00
Is there any way to analise this schedule in format:
08:00 till 9:00 1 scheduled 09:00 till 10:00 1 scheduled 10:00 till 11:00 2 scheduled 11:00 till 12:00 3 scheduled 12:00 till 13:00 4 scheduled 13:00 till 14:00 5 scheduled 14:00 till 15:00 4 scheduled 15:00 till 16:00 2 scheduled 16:00 till 17:00 0 scheduled
The data consists of 2 columns essentially, Scheduled Delivery Date and Actual Delivery Date.
If the Delivery was made on time or early, I want to count it. I will do a similar count for Late as well.
Additionally, the user can enter the Scheduled date range they want to count early/on time deliveries from. There all also blanks in the Actual Delivery date column where deliveries have not been made (no actual).
Cells O3:O38 contain the Actual dates Cells N3:38 contain the Scheduled dates Cell B3 contains the begin date to start the range. Cell B4 contains the end date of the range.
I have tried using COUNTIFS and am having trouble getting the formula to work.
I know this will not work but I hope it shows the logic I want.Count range O3:O38 where values in O3:O38 are less than or equal to the corresponding cell value in N3:N38, for a range of N3:N38 that is greater than or equal to the date in B3 and less than or equal to the date in B4.
The first 3 columns are derived from a data table and I am trying to auto populate the minutes scheduled in the time blocks using conditional formatting or some other mechanism.
I have searched and think I have found what I need to create Outlook tasks from my Excel spreadsheet. I'm hoping that someone here can help me pull it together into something that will be usable in my project tracking spreadhsheet...
I try to keep track of milestones... these milestones are listed in column B of my spreadsheet and are in rows 10 through about 105
For each of my customers I then keep 4 columns D, E, F, G - H, I, J, K - L,M,N,O - etc. Each group of 4 is a different customer....
The customer's first column (D, H, L, etc.) is a Due date of the Milestone that is found in column B. The second column is a check box that I use to indicate when the milestone is complete The 3rd column is the completion date The last colum is just a filler space that helps to separate each customer.
What I would like to do is insert a button at the bottom of EACH grouping of 4 that when pressed would look at the first column for the customer group and for ANY Milestone that has a DUE date process the stuff in the VB below to create an OUTLOOK task for each item that has a DUE date. (It would be conceivable that as it stepped through each row for a customer group of columns that I could have it create 90+ Tasks in Outlook.
A couple of additional things... in row 110 for the first column of the customer I have a DATE or a blank. So for example in D110 it might be blank or a date like 10/29/2007. If it is blank then when I press the button it would loop through ADD the tasks for each row with a due date. If row 110 has a DATE I would get a popup indicating that the tasks are already in OUTLOOK and that they were added on whatever the date is in row 110... This is kind of the safeguard that I don't get the same group of tasks multiple times by pressing the button by mistake.
Im having trouble with getting a macro to run at a specific time and day (Every Thursday at 7PM).
Here's what I have done so far..
1. I added my excel file to the task scheduler 2. created a class module and place the code below into it.
In testing the workbook opened but it asked if i wanted to enable or disable the macros - how do I get around this? And it does seem to kick off the macro (create_all).
Private Sub Workbook_Open() If Hour(Now) = 1130 And Weekday(Now, vbMonday) < 7 Then ' TheNameOfYourCurrentProcedureHere create_all If Workbooks.Count = 1 Then Me.Save Application.Quit Else Me.Close True 'save the changes and close the workbook End If End If End Sub
I have a worksheet where I calculate the duration it takes for an operation. the format of the worksheet is as folows:
The worksheet is composed of Start and Finish times in this format: 2009/06/02 5:32:19 , so for example,
- Row1 of the worksheet will have "Start" in column H1 and the start time in column B1 - Row 2 of the worksheet directly below has the "Finish" time, with the Finish time information in column B2
So with the help of excelforums I was able to implement a macro to extract the finish time cell found in row B2 and offset it to C1 and do a simple calculation to get the duration in minutes and display it in column D of the same row as "Start".
I have a workbook that contains several worksheets. One particular worksheet is a main page with buttons that open up the other worksheets for the user to view specific data. To minimize the open worksheets I have tried to institute a worksheet activate so that when the user selects this main page tab all other worksheets hide. It works well, for most of the sheets. However, for some reason a few sheets will not hide. Ive tried several different codes to get these particular sheets to close to no avail. Ive tried calling each sheet individually to hide the sheet, and Ive tried to hide all sheets together. Codes Ive used are below. Again, both codes work fine but only on some sheets. How can I get the Activate call to work for all sheets? Anyone ever have this issue? The particular sheets Im having a hard time with are in red in 2nd code.
I am using the following code in my Workbook_Open sub, located in the ThisWorkbook code module. I am enabling macros when opening. All other VBA code in the project is running correctly.
Private Sub Workbook_Open() Worksheets("Sheet1").init MsgBox "starting" End Sub
The init sub did not appear to be running so I inserted the MsgBox to confirm, but that doesn't come up either.
Over the weekend I had to look at 220 strings of numbers, some strings with as much as 20 numbers and determine if the numbers represented red, green, blue, or yellow and how many of each color from a parent list.
I did it all by hand. After getting help here from JBeaucaire on my tally sheet, which I successfully recreated with their guidance, I thought I might ask this question. How would one go about creating a macro that would when it seen a certain number, it would put the color in the column immediately to the left, and the number of colors in the column immediately to the left.
I have a project network spreadsheet shown below and in column BT I need to identify each non-critical path task (value in column BS is "N") that has a critical path task (value in column BS is "Y") as one of it's immediate predecessors.
Predecessor tasks are listed with commas separating them in column G. They are also separated out into columns H through Q for another function in the spreadsheet.
I was given the following formula by Domenic some time ago that I use along with VBA code supplied by Fin Fan Foom to open do a lookup on a closed workbook:
=If(ISNUMBER(MATCH(MIN(If( Date=D2,If(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),If(Date=D2,ABS(Time-E2)),0)),INDEX(Contact,MATCH(MIN(If(Date=D2,If(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),If(Date=D2,ABS(Time-E2)),0)),"") Everything works fine, until I attempt to… - cut & paste a block of cells, or - do a SaveAs, or - change a worksheet name
The formula will no longer function after performing any of these tasks (the cells go blank), and I have to close the workbook without saving to preserve the original workbook functionality. All other tasks, including even importing of different files into the workbook have NO negative effect.
The formula uses an “INDIRECT” named range. Sheet name changes are reflected in the named ranges, but any change of names somehow causes a malfunction (no sheet names are referenced in the VBA).
I thought that the large lookup workbook (40,000 rows) may be a problem, but even after deleting most of the data and retaining only a few rows, the problem still remained.
After the formula cuts out (after performing the above tasks), if I open up the lookup file, then the lookup will work again, but if I close the lookup file, it will stop working.
I doubt the problem is in the VBA code since IT WORKS, and besides, I have other simple VLookups formulas that also uses the same process (and VBA code) and they continue to work just fine when that one formula gives out. The only thing I can think of is that the complexity of that one formula may be an issue.
Anyway, I the original thread is long, so I’m starting a new one here. The original thread is here (Domenic’s formula, pg 7; FFF’s code, pg 8): Dedicated Cell To Choose Lookup Table
Bit of an odd one, but I'm sure it's probably happened before to others. I've just been sent a worksheet to redesign (an audit template) one which I'll be making use of data validation lists (Yes, No, N/A) a fair bit.
What I want to happen is that when the user selects from a list, I'll fire a Worksheet_Change event, which will then run some background calculations to set up the next questions, etc. Problem is, the event isn't firing at all. I'm using the following to test the event...
I wrote a simple script to show/hide certain rows based on the value of a certain cell on my worksheet (cell value chosen by drop down). When I left work last night, everything worked fine. When I returned this morning, the change event no longer appears to be firing. I'm quite certain no one else accessed the file to change the coding, so my only guesses are 1)perhaps some sort of system update was applied in the middle of the night and it messed with something or 2) aliens have blocked our technology in advance of their invasion.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next
If Target.Address = "F1" Then
Application.EnableEvents = False
Select Case Target Case "150" Rows("13").EntireRow.Hidden = False Rows("14:19").EntireRow.Hidden = True Case "330" Rows("14").EntireRow.Hidden = False Rows("13").EntireRow.Hidden = True Rows("15:19").EntireRow.Hidden = True Case "340" Rows("15:19").EntireRow.Hidden = False Rows("13:14").EntireRow.Hidden = True Case Else Rows("13:19").EntireRow.Hidden = True End Select
i try with what limited knowledge i have, if you dont mind take a look at the code below, i read your article and added the appropriate line, the code works fine except the msgbox has to be ok'd twice before it exits sub any ideas why?
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rng As Range Dim MyCell Set rng = Range("A2:A100") If Not Intersect(Target, rng) Is Nothing Then With rng For Each MyCell In rng If MyCell = "" Then MyCell.Select MsgBox "Please use this next blank cell" Exit Sub
I am trying to drag a numerical value down (X4-X10) but I need it to repeat two rows in between, simultaneously keep the numbering in sequence only after the repeats 2 rows in between, and perform the previously mentioned 2 tasks any row in a column. So far, I have to manually enter or when I select cell X4 and X5 together ( having a value of 1 and 2 respectively) and drag, the value does not repeat 2 rows in between and does not keep the numbering in sequence after the 2 rows.
At Present: X4 1 X5 2 X6 3 X7 4 X8 5 X9 6 X10 7
I need to have
X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ] 1 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+1 1 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+2 1 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+3 2 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+4 2 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+5 2 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+6 3 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+7 3 X [nsubscript1, or nsubscript2, or nsubscript3... nsubscript10000 ]+8 3
and so on...
I have a 2000+ rows to manually input and hence it is not practical!.
I have had some help with this (thanks!) but am stuck.
Can someone add to the code below and make it so that multiple tasks, with multiple assignees, can be created from a range of cells, such as:
Column A Column B Row 1Task SubjectAssign to Row 2GV LP for AZJohn Brown Row 3GV LP for COJohn Brown Row 4GV LP for ILSuzy Smith Row 5GV LP for INSuzy Smith Row 6GV LP for KYSuzy Smith Row 7GV LP for MNSuzy Smith Row 8GV LP for MSBob Barker Row 9GV LP for NCBob Barker Row 10GV LP for NJBob Barker Row 11GV LP for NMBob Barker Row 12GV LP for NVBob Barker Row 13GV LP for NYBob Barker Row 14GV LP for OHBob Barker Row 15GV LP for SCBob Barker Row 16GV LP for WVBob Barker
(Sorry, it didn't translate well. The "GV LP for AZ" is cell A2, "John Brown" is cell B2.)
I want to uninstall an addin from another addin. The problem arises when the addin i want to uninstall contains events similar like auto_open and workbook_addininstall It seems that they get triggered even when i close the addin That way i loose controll over the programsequence. And that is the thing i don't want. If someone has a solution, that would be great. Otherwise i have to reorganize everything and merge two addins in to one And i really don't like all the extra work
I have a spreadsheet with a table of values in range E5 to T158.
A macro populates the table by looking up values on other sheets in the book. If the macro finds a value in the lookup for Row 7 of any column (ie E7,F7...T7) it populates the rest of the column with that value (E7 value gets pasted to E8:E158) THEN it protects the cells it pasted (E8:E158).
If the macro does NOT find a value for row 7, it simply skips it, leaving it blank, and continues to row 8 until it reaches row 158 of each column E to T.
I want to give the user flexibility with these values. So if the user either deletes E7 or changes the value of the contents in E7, I want to unprotect the cells of rows 8 to 158 for that column.
I have created a
Private Sub Worksheet_Change(ByVal Target As Range)
in the private module for that sheet below. I thought it was working but it isn't doing anything when I change or delete the value in Cell E7 for example. Please help!
Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if more than one cell is changed If Target.Cells.count > 1 Then Exit Sub 'Or IsEmpty(Target)
