I need to know how to make a workbook (and any/all copies made there from) cease to function (become irreversibly read-only) after a specific period of time following the first time it is loaded by a user (this would be preferable), or after a specified date (this could also work).
View 9 Replieshow to stop macros running on a particular worksheet within a workbook. I have a workbook monitoring vehicle faults with a control sheet. The control sheet displays the reg no and a "traffic light" indicator highlighting faults next to the reg no. Traffic light is turned red and green by If blank control. Each vehicle has a worksheet where faults can be recorded and a menu bar at the bottom of the screen operates numerous macros to "email worksheet"-"clear and copy to history" etc etc. The menu bar though can also be viewed and operated while in the control sheet. If a macro is activated within the control sheet it destroys it as it was not designed the same as the worksheets. Can I programme the menu to be inactive when in the control sheet OR that the menu will only appear when in every other sheet.
View 3 Replies View RelatedI am using excel 2013 in my laptop (windows 8.1) after having upgraded excel 2007. Everything works fine during my daily tasks. Especially autofill which I use quite often works perfect.
At the same time I upgraded my girlfriends laptop (Windows 7) with the office 2013 (office 2007 was the older version) and somewhere there begun some isues. Autofill doesn't. Especially with months or days. I enter the value "Monday", I drag down (of course using the black filled cross down right of the cell) the cursor and all the cells get the value Monday. Even if enter the value Tuesday in the second cell and then try to autofill (having marked before both these two cells) I get the same results. Monday, Tuesday, Monday, Tuesday.
I tried to test the autofill with numbers. If i write only the one number (for instance the value "1") and then autofill, this doesn't work. It gives me the value "1" in all the cells. But if I enter the value "2" in the second cell, the autofill works fine (marking these 2 cells and then autofill).
We make some online courses using exactly the same excel exercise files. In my laptop everything works perfect.
I have a macro that I've been using for a while now for one workbook that I want to use in another workbook for the same purpose. But, when I try to run it in the second workbook it gives me a Runtime error (32809). Here is the macro:
Sub invisible()
If Range("G16").Value = True Then
ActiveSheet.Shapes("OptionButton13").Visible = False
ActiveSheet.Shapes("OptionButton14").Visible = False
Range("A36").Value = "Blah Blah Blah"
Range("C36").Value = ""
Range("C37").Value = ""
ActiveSheet.Shapes("Check Box 182").Visible = False
Range("I11").Value = "Blah Blah"
Range("K11").Value = ""
Range("B36").Value = True
Else
ActiveSheet.Shapes("OptionButton13").Visible = True
ActiveSheet.Shapes("OptionButton14").Visible = True
Range("A36").Value = "Blah Blah Blah"
Range("C36").Value = "No"
Range("C37").Value = "Yes"
ActiveSheet.Shapes("Check Box 182").Visible = True
Range("I11").Value = "Blah Blah"
Range("K11").Value = "100"
Range("J11").Value = False
End If
End Sub
It is giving me the runtime error on this line:
ActiveSheet.Shapes("OptionButton13").Visible = True
What could be the problem?
excel.jpg
how to make my network days stop counting when I insert a value in the completed column (in this case H3). I have a Date Received column (A3), a due day column (G3) and the last column with the date (N3). My remaining days column keeps counting after I filled the completed cell (H3) and won't stop counting after. I need a formula to stop after the completed cell is fill up.
I am using Excel 2010 and I am not able to use the excel clipboard.
There is no problem with the cut/ copy and paste function, however, the items I copied/ cut cannot be collected to the clipboard for my further usage.
Say in column A, I have the values 1,2,3. If I set cell B1 equal to A1 and drag down, I'd expect this:
A B
1: 2 2
2: 3 3
3: 4 4
But all the columns in B just say 2. What function did I accidentally change?
I've created a very simple formula which counts the number of days from Today and excludes blanks.
I'm trying to set it up so the formula *stops* counting the days once a cell in column C is populated.
Formula so far...
Code:
=IF(A3="", "",A3 - TODAY())
It looks exactly like this this [URL] .....
How do I stop a user hiding a workbook using VBA? I have tried:
Private Sub workbook_deactivate
If windows(myfilename).visible = False Then
windows(myfilename).visible = True
End If
End If
This works but when I try to close all files sometime I have to press the top cross twice. This may be because one workbook references the rest.
I need to stop the calculations when the workbook opens. I have tried
View 2 Replies View Relatedi am using the following code to close my userform and work book,this disables the exit button on the userform but i can still close the workbook by using the exit buttons on the sheet. can i disable the exit buttons on the sheet so the only way to close the workbook from the userform
Private Sub CmbClose_Click()
Dim ans As Variant
ans = MsgBox("This will close XL, save current file?", vbYesNoCancel)
If ans = vbYes Then 'save book and quit XL
ThisWorkbook.Save
Application.Quit
ElseIf ans = vbNo Then ' no save and quit XL
Application.Quit
ElseIf ans = vbCancel Then 'no save, no close , back to userform1
Exit Sub
End If
End Sub
I have a report whose calculation time I've reduced greatly by turning off automatic calculation and including in the worksheet code directions to only calculate the impacted range. BUT, it still takes a lot of time to calculate on close, when I turn automatic calculation back on. I'm afraid my users will think their machines have crashed. Does anyone know of a way so that on close, the workbook does not calculate, even though Calculation is turned back on to automatic?
Private Sub Workbook_Activate()
With Application
.Calculation = xlManual
.MaxChange = 0.001
.CalculateBeforeSave = False
End With
End Sub
Private Sub Workbook_Deactivate()
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
End Sub
I wanted to stop users from using cut and paste as well as drag and drop so I found something in Ozgrid to do that. (http://www.ozgrid.com/VBA/disable-cut-copy.htm)
Problem now is I am trying to run a macro that will copy and paste certain values, but because of the code I have added in the above link, whenever I click on a cell and copy it, when I click any other cell, the copy area gets cleared which of course disallows me from pasting it.
Private Sub Worksheet_Activate()
Dim oCtrl As Office.CommandBarControl
With Application
.CopyObjectsWithCells = True
.CellDragAndDrop = True
.CutCopyMode = True 'Clear clipboard
End With
'Enable all Cut menus
For Each oCtrl In Application. CommandBars.FindControls(ID:=21)
oCtrl.Enabled = True
Next oCtrl
'Enable all Copy menus
For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
oCtrl.Enabled = True
Next oCtrl.....................
I want to stop the formula auditing from working in one workbook without having to change settings in excel, is this possible?
View 3 Replies View RelatedI've got a macro embedded in a sheet (not in a module)..."Private Sub worksheet_activate()".
I have a macro that copies this specific sheet to a new workbook in order for other users to play with.
Problem is, when they select the new copied sheet in the new workbook, it tries to execute the "worksheet_activate" macro.
I've done searches on trying to delete macros but to no avail....
I´m using this code to create a messagebox±
Private Sub Workbook_Open()
Dim c As Range
For Each c In Range("E2:E126")
If c > -15 And c < 0 Then MsgBox c.Offset(, -3), vbOKOnly + vbInformation, "LET OP! Einddatum contract nadert!"
Next
End Sub
The messageboxes may only appear when the workbook is opened. The problem is that they appear when I change something in the workbook.
Currently I am using VBA Code below to Stop Update links prompts
Private Sub Workbook_Open()
Workbooks.Open "C:TempBook2.xls", UpdateLinks:=False
End Sub
Is there code I can use such This Workbook so that I can save this file as another name and it will work? Biz Auto Merged Post;Dear All, I have found VBA code that works.
Private Sub Workbook_Open()
Workbooks.Open ThisWorkbook.Name, UpdateLinks:=False
End Sub
I have a series of data that acts upon a traffic light system, i.e. Green, Amber and Red. These variables are posted along row 1 for example and there are 10 columns. Per column I have a tick and cross to answer a question. How can I find out how many ticks were on green days, amber days and red days? I have attached an example.
View 4 Replies View RelatedI need a macro that records users accessing a workbook over a network and if I have not logged in after a 30 day period it will replace all calculations with data and if possible wipe all macros.
I have tried to nut this out but time is against me.
I have had my work taken to other sites within our business and passed of as their work, and it bugs me.
Is there any way to have a input box pop up after a Prescribed number of days or on a particular date and from that point on prompting the user to enter a password that I created (hard coded into the code)In order for the workbook to open If the password is not entered or is not correct then the workbook will close.After the pasword is enterd then the workbook would open from that point on(the input box would be disabled) Also this macro would have to be password protected in order to prevent the user from accessing the code.
View 8 Replies View Relatedmy excel sheet runs through a lot of calculations, opens Flowmaster, a simulations program, passes on data, receivs data and so on. Is there any way to have a user input to stop the whole simulation. During the first tries I had a lot of break point in my debugger. But now I want to have a button to hit or better just some keys to hit to stop it without using the ctrl+alt+del which closes everything.
View 4 Replies View RelatedThere are dates in column C and I need to count how many days are coming due within 90 days of each date based on the today() function but do not exceed the 90 days.
Countif Today()+90
I am making process TAT(Turn Around Time) which required following information. In Excel 2007.
1-Count number of days between two dates where working days are (Sun to Thursday). So required to exclude (Friday,Sat + Holidays)
A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 22
D1-Days between two dates 21
E1 To E10-Holidays
2-Count number of days between two dates where working days are (Sat to Thursday). So required to exclude (Friday + Holidays)
A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 27
D1-Days between two dates 26
E1 To E10-Holidays
Note : Any weekend (off days) dates listed in holidays should not effect the query.
I'm basically looking for a forumla that will count each employees total scheduled work days for the month inserted and then depening upon the day it is will show how many days the employee has left to work for the month.
View 9 Replies View RelatedHow can I convert the number of days for example 366 days to years, months, days 366=1year, 0 months, 1 day
View 3 Replies View RelatedI have a spreadsheet in 2007. Days across the top for an entire year. So 365 columns. I need a macro to extract the 3 previous days , today's column, and the 3 next days and load to a different worksheet.
HTML worksheet A:
1/1/09, 1/2/09..... 10/17/09, 10/18/09, 10/19/09, 10/20/09, 10/21/09 .... 12/31/09
10 11 ... 14 15 17 12 22 ... 28
a g ... g c d d a ... a
the macro would create a worksheet B(if run on 10/19/09):
10/17/09, 10/18/09, 10/19/09, 10/20/09, 10/21/09
14 15 17 12 22
g c d d a
I've got the below so far, but where it says V2>=(Q2+5) , I would like it to add 5 working days instead, is this possible?
Code:
=IF(AND(T2="Awaiting",V2>=(Q2+5)),"Overdue","Raised within 5 Days")
It would also be great if there is a way to say that if T2="Not Required, then put "Not Required" in W2 (where the IF statement is)
I need a formula to count how many days hours & mins between 2 dates but included in the formula need to ensure it only counts business days.
Column A 02/09/2011 13:00
Column AO 02/09/2011 13:02
I need in Column AP to show 0 00:02
The formula I have tried using is - =IF(AO3="","",AO3-A3) which doesnt work as instead of showing 0 00:02 it shows 1 00:02. I dont want it to count as 1 day just 2 mins.
Once that has been worked out I need to include WORKDAYS formula to only count working days. I have created the list on 'Controls' Tab Column A10:A47.
merge these together to create the one formula?
I want to calculate the end date of my German courses. This is how it works:
A course consists of 60 LU*. The course can occur i. e. three times a week: Monday, Wednesday and Friday. In each day the course lasts 2 LU, which means 6 LU each week. There is no course on Tuesday, Thursday, Saturday, Sunday and on holidays. Therefore this type of course that begins on 18-Nov-2013 will end on 03-Feb-2014.
Another course which occurs Tuesday, Thursday and Saturday, and respectively has 2 LU on Tuesday, 2 LU on Thursday and 3 LU on Saturday and starts on 03-Dec-2013 will end on 06-Feb-2014.
Therefore I want to create a worksheet where I set the start date, choose the days and respectively the LU amount on those days. The end date shall be calculated according to these criteria.
The workday function on excel cannot do this and I do not have any programming skills to work with VBA.
Legend:
*LU = lesson units; 1 LU is 45 minutes
Holidays:
28-Nov-13
29-Nov-13
08-Dec-13
09-Dec-13
25-Dec-13
31-Dec-13
01-Jan-14
[Code] ...........
I wanted to determine the number of days between two dates. Specifically, if the initial date is in one month, and the second is in a different month and an output would result a number of days in each month until the final date. BUT I have a large amount of data to do this for in a list view, way to put a formula in excel and just drag down the entire list to get the required information. see below for an example.
The result I'm looking for is the separate the months and only show the relevant months between the two dates in one cell or the adjacent. Something similar to the table outlined below.
Input Data
Result
Start Date
[Code]....