I've the following code in my add-in. Strangely the code in the Workbook_Open event executes as expected, but not the Workbook_beforeClose event. May I know the reason?
*********************************************
Private Sub Workbook_Open()
msgbox "hi!"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
msgbox "bye!"
End Sub
I've been trying to figure this out all morning, and it's giving me a headache. I'm trying to write some code in the WorkBook_BeforeClose module as shown below:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans As Integer
ans = MsgBox(prompt:="Do you want to save the changes to " & ThisWorkbook. Name, _
Buttons:=vbInformation + vbYesNoCancel, Title:="X2O")
Select Case ans
Case Is = vbCancel
Cancel = True
Exit Sub
Case Is = vbYes...............
As you can see, I show a message asking the user if they want to save the workbook or not. Clicking the Cancel and the Yes buttons work fine. However, the No button causes the message to be shown again. If I then click No the second time, the workbook closes!
I'm trying to use Workbook_BeforeClose event to delete my custom menus.
However, when I try to pass the custom menu caption to the Sub, I get a compile error (Expected Function or Variable).
I believe this has something to do with global variables declaration or something...
I read two pages on "Workbook_BeforeClose" trying to find a relative answer. I want to able to close a workbook with out saving and not rely on the user pressing the correct button, can this be done?
View 3 Replies View RelatedI have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i
I was looking for a final result as follows
21-Aug-09 + 1 = 24-Aug-09 (Day + next 1st working day)
21-Aug-09 + 3 = 26-Aug-09 (Day + next 3rd working day)
I created a spreadsheet with many CF's and save the sheet on our network, however, when my manager tries opening it the CF's do not work. they are there when I click on CF > Manage Rules, they just not working, neither is the Data Validation Drop Downs?
View 7 Replies View RelatedIn attached file I need doing sum where total working hours are not showing correct.
TotalingTime.xlsx‎
In A column the date something is received will be entered.
In B column there is a drop down list that has "insured" "Fee" "Aged Debt" "Other".
In C column I am trying to get another date to populate dependant on what is picked from column B, so if Insured is picked then 1 working day needs to be added to the date in column A and populate in column C. If any of the other 3 options are selected 5 working days needs to be added to the date in column A and populated in column C.
I have been using this formula: =IF(B2="","",IF(B2="Insured",A2+1,A2+5)) however this is not working days and I am stuck how to get to add working days
I have a dropdown answer column (J) to select an answer and in column (K) next to it that scores the answer. I'm trying to sum the score of (column K) in cell K7 but can't seem to get it to work.
View 3 Replies View RelatedI defined few name in manager but that are not working. I Defined names with multiple sheets with same Column.
See the attachment : Demo.xlsx
I think this may be impossible but what I am hoping to do is in B3 Return the date that is 3 working days before the date in B2. Working days being Monday to Friday in this case. so if the date in B2 is the 16/04/2014 3 days before this is the 13/04/2014 which is a Saturday so i would want the formula to return the 12/04/2014 which is the Friday. Is this possible?
View 2 Replies View RelatedI'm using Excel 2007.
Is it possible to know which row code is active on whilst it is running?
s/s is 332324 rows and growing. When I run code which goes down one column and takes quite some time I don't know what stage it is at.
I'd like to be able to see on screen which row it is down to.
I have a column with hours in the following format HH:MM:SS, that looks like this:
12:16:00
1:35:00
1:35:00
12:47:00
12:46:00
12:51:00
4:33:00
4:05:00
12:54:00
3:19:00
3:19:00
I am trying to add the sum total of all hours, but its not working. I have my column formatted as time -> 37:30:55
=((SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--(ISNUMBER(MATCH('Data Sheet'!$B$2:$B$997,{300100,302100},0))),('Data Sheet'!E$2:E$997*IF('Data Sheet'!E1<=$F$4:$G$4,1,0))
The last part of the formula is the problem, the first two arrays are conditions and the last array is a sum if f4 a period of the year is equal or less to the data sheet tab it is summing.The formula below works fine, but when the last condition is added it doesn't work.
=((SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--(ISNUMBER(MATCH('Data Sheet'!$B$2:$B$997,{300100,302100},0))),'Data Sheet'!E$2:E$997)
I am using and index match formula that looks at names and dates. The dates are fine. The names are fomatted the same in both lists. Two names identical in each respect give me a #N/A result. It should match but it does not. How do make each formatted the same. Very strange......
Example:
Chuck
Chuck.
Cells H5:S5 have a formula in it to determine whether or not the allocation calculation needs to be made. I figured it to be straight forward enough
View 3 Replies View RelatedThe following is a simplified example of my problem: I have a list of product item numbers in column A. Next to each item number in column B, is a hyperlink to a PDF file relevant to each item. Elsewhere in the sheet, is a drop down box containing the item numbers. Next to the drop down box is a HYPERLINK(VLOOKUP) that fetches the relevant hyperlink depending upon the selected item in the drop down box. Problem is, when I select the ‘lookup’ link, I get an error message ‘cannot open the specified file’ (the original Hyperlink works fine). Does anyone have a solution to this problem (preferably without resorting to Visual Basic?
View 13 Replies View RelatedSometimes when I right click on a worksheet (filled with formulas, formatting, etc)and select move or copy and then click on "create a copy" a new BLANK tab is created rather than a new worksheet with my data. I'm not sure why this happens sometimes. This usually happens from a spreadsheet I downloaded from a website that I work on.
My workaround is just to copy and past the whole sheet to the newly created tab, but it's frustrating that it doesn't work the normal way.
Got this neat Calendar from another thread on this site,but it has problems. Played around with what little skills I have with no luck. When you open the calendar go to Jan, 1992 click on first four days and everything works. When you click on Thu 2 the problem begins.
View 14 Replies View RelatedI notice that sometimes the "Go To" function does not work. When I click on the named range Excel just brings the cursor to a the first cell of a worksheet rather than the actual cell/range. I find that I need to restart Windows in order to fix this. A simple restart of Excel does not work.
View 4 Replies View RelatedI can not figure out why only the color index 5 and 8 are working in the example- (1). To tun it say "no" to the initial prompt that comes up and select 1 from the dropdown in A2. Before some additional functionality was added it was called TestBook and that highlighted appropriately.
View 2 Replies View RelatedIs there a way in excel to say if i select Item1 and it is 100mm x 30mm x 100mm, excel will say this will fit into a compartment called A1 and may also fit into a compartment called B6.
I have a list of all the compartments and what the internal dimensions are. So is it just a matter of saying if these dimensions are within those of the compartment then that item will fit in.
I have a list of some what 6000 Items that have dimensions. I may pick 200 or so of these items to fit into special compartments of a machine. Because of the varying sizes i need to know what compartment each item will fit in so i can build a machine big enough and get the right size compartments.
Where do I find a tutorial or examples to show me how to create bars for gantt chart in excel, based on date values?
i dont know what's wrong with these lines:
View 3 Replies View RelatedOnError not working
Here's the code where I'm having problems:
Once the intended chain of actions is performed, normally EXCEL would go on to display the contextual menu, (which is its standard right-click action when no macros are involved).
Since I don’t want my macro operation to end with that menu covering part of my screen, I have the macro ‘type’ in ESCAPE, through one of these lines:
I have a Sheet which has a Header Row which consists of Dates like this: ....
View 14 Replies View RelatedCreate a column of concatenated values to serve as a "primary key" for a compare and merge tool (Synkronizer). For some reason, the CONCATENATE function isn't being recognized.
If I enter
=CONCATENATE(A2,B2)
instead of showing the strings located in A2 and B2 it displays
=CONCATENATE(A2,B2)
in the cell.
I tried creating a new spreadsheet and it works just fine. Does anyone have an idea of what might be happening? I have compared all of the options between the two spreadsheets and don't see any difference in them.
I created a filter with a macro.
It is working on my computer but not on the one of my colleague. After running this macro, on the computer of my colleague, it shows nothing. I have to manually re-run the filter on the date.
The date filters is filtering on date previous than todays date...
Code:
Sub Macro13()
'Delete row if a cell in column B is empty
Sheets("Projects Variance").Select
Application.DisplayAlerts = False
Range("E1:E65536").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'Filter with dates previous than today
ActiveSheet.Range("$A$2:$T$10000").AutoFilter Field:=14, Criteria1:= _
"