Private Sub Workbook_Activate()
Dim user As String
If Worksheets("Setup"). Range("D6").Value = "" Then
user$ = InputBox("Hello. Please enter your name to inialize the program", "Enter Name")
Worksheets("Setup").Range("D6").Value = user
MsgBox ("Welcome " & user & ". Press 'OK' to continue on to the Main Menu.")
Else
user$ = Worksheets("Setup").Range("D6").Value
MsgBox ("Welcome back " & user & ". Press 'OK' to continue on to the Main Menu.")
End If
Exit Sub
but i would like to expand the "welcome back" msgbox to also display "the last time you were was was (eg: 2 days/3 hrs/14 min) ago" - which i presume would be calculated from the last save.
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 need a formula to calculate how many months has passed irregardless how many days had passed. What I mean by that is if I have a starting date of 1/31/2010 and an end date of 2/1/28/2010, that should count as 1 month passed. I tried using the DATEDIF function, but that function is counting number of days, so if only 28 days had passed, that would not count as 1 month.
I'm looking for a way show the username of the person who last saved the spreadsheet, and the date & time it was saved. I would like to be able to ouput this data in certain cells on the worksheet. I have tried using something like:
I have a macro that returns the time the sheet was last saved:
Function LSDate() LSDate = Application.Caller.Parent.Parent. _ BuiltinDocumentProperties("Last Save Time").Value End Function
Unfortuantely, the cell doesn't update without recalculating the cell. Is there a way to get the cell to update automatically every time the sheet is saved?
Is there any way of creating a log that logs the last user that saved the workbook including the date and time. I would like the log to be a sheet hidden in the workbook. Not many people will be saving the workbook.
I want to highlight the rows in my worksheet when the dates in column 'N' are in the past. I've seen codes to do this using conditional formatting when searching online and in here but the problem is is that it highlights blank cells as well. Is it possible to correct this?
I would like to know a way to sum the future months dollars only once the month has passed to consider that amount only in my forecast. For eg. If I have a Vendor A contract from Jan - April for $1000/per month in total for $4000. My Forecast should only be Feb-April = $3000. So my total column should only display $3000. Once Feb has passed , the forecast should only be March-April i.e $2000. How to get rolling month sum of forecast once month has passed.
Attached is a sample spreadsheet with different vendors.
I am using the above formula but would like it to get the value when the date has passed (when date has been inputed in another cell)
I have added an attachment, The formula begins at J44 to AY44 but when date has entered in the red (D44:D67)section I would like to retreive values for blue section(Q44 and onwards) and not the yellow section(J44 to P44)
This is so when players make a transfer it doesnt include the weeks before that date!!
e13 = 0 until after the 10th of the month on the 11th it = $100.00 If I13 has an amount in it on or before the 10th e13 remains $0. Each of the 12 line of this rent roll needs to correspond to its own month.
I have a worksheet that has a drop down box with the options PPP, MONTHLY, TOKEN & DEFAULT, next to this i have a date when a payment is expected, what i want to do is automaticaly set this to default after the date has expired, the problem i have is that there is data validatrion in this box so i cant enter a formula, is there a way around this
Here I have 9 total requirements to be tested in any event. If I wanted to count the number of events passed it wouldn't be 7. See Blah2 it passed Design and Electrical. Separate Events yes, but only one requirement. I need to have a total could of 1 for that as passed. Not two. I'm comfusing myself because this inherited spreadsheet is over 1,000 requirements.
The following afterupdate procedure for my txtStart1 text box sends the value to the FormatTimeValue function that is coded in a module.
Private Sub txtStart1_AfterUpdate() MsgBox Len(Trim(Me.txtStart1.vaue)) '<= outputs 1 Me.txtStart1.Value = FormatTimeValue(Trim(Me.txtStart1.Value)) End Sub
As you can see from the bolded text, the length of the value is 1 when outputted from the forms code.
However, when I pass it to the following function in a module it outputs 2 as the length.
Function FormatTimeValue(vTarget As Integer) As String
Dim TimeStr As String
If IsNumeric(vTarget) Then
MsgBox Len(vTarget) '<= outputs 2
Select Case Len(vTarget)
Case 1 ' e.g., user entered 1 so time should be 01:00 TimeStr = "0" & vTarget & ":00"
All the subs below take place in Module4. I have a variable, MoreDates, defined as Boolean. At first it was just dimmed, now I have it as Public. When it's passed to a sub, it's true; in the called sub it's changed to false (and I see it as false when stepping through) and sub is exited. The next line of the calling routine checks if true/false...but it's true again. I'm not passing by value, so I don't know why this is happening.
MoreDates=True Do Until MoreDates = False Query_BigCharts (MoreDates) '*************** If MoreDates Then temprow = temprow + 1 Range("A" & temprow).Select lastdate = DateAdd("m", -1, lastdate) ' backs last-written date up a month Range("D6").Value = lastdate 'send to eom cell lastdate = Range("D8").Value ' formula in d8 converts to last weekday of the month. End If If lastdate < FromDate Then MoreDates = False End If Loop End Sub.............
In my excel sheet, I have date values stored in cells from D7 ti IV7. in cells C8 to C100, I have a data validation which selects the values from cells D7 to IV7. When user selects a particular date in coumns C8 from the list, using my code, I am trying to search for the value stored in cell C8 within the range D7:IV7 using function Application.Match
While running the code by clicking on "Distribute Budgeted Efforts" command button, I am getting an error "Type Mismatch" and error is coming from code line number 27 which is "lInitial = Application.Match(lInitialdate, Range("D7:IV7"), 0) - 1"
My code is as below
Private Sub cmdEffortDistribute_Click() Dim lCount As Single Dim lStartDate As Date Dim lCounter As Single Dim lBudget As Single Dim lInitial As String Dim lInitialdate As String Dim lInitialWeek As String
I am also attaching excel sheet which I am using here for reference.
I have a query where I am trying to find out how many people passed in green, amber and red from a series of data. I know (from the attached file) how to find out how many greens, ambers and reds there were, but how can I find out how many of the 7 in cell B12 passed in Green?
I am trying to devise a service schedule that has the installation date in say cell D4 and I would like this to automatically place a date a year from there in to cell E4, then 4 weeks before date comes round i would like the text cell to turn yellow, then on & after the date to turn red. Is this even possible?
I am working on a sheet that has over 150 tabs of information. It is a simple audit that allows for Yes, No or N/A to be selected for each audit category. I have an Outcome Summary tab that is pulling totals for all the audit information on each tab. I did a CountA statement to count any cells (from all worksheets) that had anything selected, so I have my count for the amount of files audited, but for the life of me I cannot come up with a formula that counts the ones that passed or failed the audit. Needs a formula that counts for Yes as 1, No as 0, N/A as 1 (these don't count against us) and doesn't return an error for a tab that wasn't selected.
I want to make a sheet that will lock cells from rows that have passed the yesterday date. In A column i entred the days and the locked row must have the range from A to U. After i read the post from here Lock / Protect Rows Past Due Date any solution.
Have a macro where is some dates have passed the formulas are cleared but if the date is < than TODAY the value is left. All worked ok until I changed to formula to where the date calulated exclued weekends. Have attached example spreadsheet. Forgot to add the marco was provided by the hard work of fellow Ozgrid memeber (Ger).
how to display the result i made in macro to active worksheet so that i can keep a record of the result made in my random generator,
Sub timer() TextBox1 = "" TextBox2 = "" TextBox3 = "" TextBox4 = "" Dim x As String, i As Byte Randomize x = Format$(Int(Rnd * 1899) + 1, "0000") For i = 1 To Len(x) Me.Controls("TextBox" & i).Value = Mid$(x, i, 1) Application.Wait Now + TimeValue("00:00:03") Next
From what u can see here, its a massive formula.... but I am pretty sure that there is a shorter way to key in the formula
*Note* 'Team Schedule'!D5-D12 and 'Team Schedule'!H2-H4 are PH. disregard the 4.5 that is in the formula as it is used for other purposes. Every PH that has passed will credit a 1.5 into the cell.
This might be a simple answer but... if A1 had the date 1-Jan-2010, what formula would I used, within conditional formatting, to make that cell turn blue once 90 has passed? So the cell should turn blue on 1-Apr-2010.
I am trying to lock cells after today's date has passed so that no one can make changes to it after today's date has lapsed. This is for protective reasons so that people do not remove their names from reserving something after using it. Now the code should disallow locking after cell input entry when today's date hasn't passed so that changes can still be made by the user. I am trying to determine the code to do this but I have no idea as to how to do it.
Here's a scenario: I reserve something for Aprill 11, 2009. I input my name. Since it's April 9th, 2009, I am still able to make changes up and until April 11, 2009. After this date, the cell is locked and no changes can be made, except for the administrator.
In column A of a database I've calculated workdays for all year (excluding weekends and public holidays).
Every day, I update the numerical data of the database, with the facts of previous day.
In another sheet I've made some monthly, quarterly etc., reports for that data.
What I want, is to have:
1) a report every day, with the sum of specific data (say that in column D), for a period starting the first workday of current month and ending the previous workday of today.
The code as it stands allows me to push a button and it immediately picks up on a meeting category and date of meeting, then sends emails to those that are supposed to attend based on a date that is within 7 days from the current date of pressing that button. HOWEVER; it seems to still send the email when the date has passed...is there a line I can put in this code to make it so that every date that has already passed will be discounted from future emails?
