I have a workbook which autosaves and closes if no data is input for 10 minutes. It resides on a drive accessable by all our team and is set so that only one person can have read/ wrote access at a time. It tests for read only, shuts down if it is and saves and shuts down if not.
I have noticed that it doesn't work if the workbook is waiting for input.
I.e. someone starts to enter text, or something, in a cell, then goes off to make a coffee or whatever leaving the entry cursor sitting there blinking. It will not shut down after the ten minutes with no input.
I have a workbook with several worksheets. The numbers entered by the user in the cells C7:C9 of sheet "A" will be used to calculate some values in sheet "A" as well as in sheet "B".
Calculations on both sheets involve macros: macro "scale" on sheet "A" and macro "sort" on sheet "B".
I used this code to execute macro "scale" on sheet "A" triggered by the Worksheet_Change function:
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C7:C9")) Is Nothing Then Exit Sub Else scale End If End Sub
now, I want to execute macro "sort" on sheet "B" practically at the same time (with the same worksheet_change condition for sheet "A")
All off my attempts resulted in running "sort" in sheet "A" which is not what I want. I couldn't get it to execute the macro in the other worksheet. Already tried "With Worksheet("B") and "Dim ws As Worksheet, Set ws = Worksheets("B") - no luck...
I have finaly completed my project to include all the bells and whistle needed to make an ok task a bit more exciting. However, i am having a problem when the code is executing. It seems to "break" at random points in the process. I am not able to duplicate this error at my home (nor do i want to) and I can not quite figure out what is causing this as it only happens at my work computer. I have tried my coworkers computer and the code executes flawlessly like at my home.
Strangely enough, when the code breaks I am able to hit 'continue' and it continues on its way until the next hiccup. Please assist as this fluke is causing my brain to spiral.
if I can send text to a macro and have VBA treat the text as a line of code?
Suppose in a worksheet in cell A1 there is the following "text" msgbox "hi there"
can you get vba to do the following:
Sub runtext() Mytext = sheets(1).range("a1").text XXXXXX Mytext End Sub
where "XXXXXX" is a method or command that will cuase vba to evaluate the text string and run it as a line of code (returning a message box saying "hi there")
I don't want to send variables/objects to an existing method in VBA - I want to send the method to VBA....
I am trying to find an item within an array and then VBA could execute code.
Here is an example:
[Code]....
LookupItems =("text", "value", "book") or should I acutally be using Split("text,value,book",",")
For Each sht in ThisWorkbook If sht.name = array(LookupItems) Then.....execute code
[Code] ....
So basically in this example I want to loop through all the sheet names in the workbook and if any of the names in the arrary are found it will execute the code for those particular sheet names.
I know alternatives are the select case or write an if statement for each value I am looking up or even use an OR for each value to lookup; but I just wanted to see if this method was even possible as it would be less coding.
I have a file I created which works fine for me, on my machine at least but there are two issues I'm trying to sort out.
The first issue is I need to share the file with others who may not have the same access rights. I have something loading in the Workbook_Open event which I want to bypass for users who do not have access to a specific folder. So I was trying to check their access using Dir(path) = "" at the top level folder to see if any files are present (which there are). My assumption was that if it finds files it's safe to assume they have permission and it's ok to continue, otherwise they don't in which case I want abort the rest of the code. This seems to work fine for me but I tried testing with someone I know does not have access and it gave her a compile error.
Part of the code in macro utilizes FileSystemObject which seems to be the line where the code bombs on my coworkers machine. The FSO seems related to appears related to the Microsoft Scripting Runtime reference, but it's not enabled on my machine or any of the others. I vaguely remember having to do something on my machine awhile back for the life of me don't remember what it was.
The second issue is an MS Forms error: "Could not load some objects because they are not available on this machine." appears when opening the file on three of the machines I tested but it does not appear on my machine. I've tried looking at the references and could not find any differences between the machines. I pored through various postings online and it appears it might be worth re-registering the DLL/OCX files but we need admin rights to do that so I will need to open a request with our support team.
I have written a vba code which does some calculation on the data on sheet 1 and then puts this data on sheet3 and after the calculation is done it puts the result on a few comboboxes on sheet2. Now the issue is while this whole calculation is performed by excel, I can see the movement in between the sheets. I want to avoid seeing this movement ?
Is it because of multiple Sheet.Select statements inside the vba code ?
I cannot Save my updates and fixes to that Workbook only.
'The macro code uses Sub Macro1 () ' over 1000 lines of codes, Loops/Do Until etc '5-10 worksheets '3-4 modules with various calculations Activeworkbook. Save Macro2 End Sub
I transfered everything to a new workbook, it ran and Saved ok for a while, but each time I add more lines of code the same error will occur and a message will appear with "sorry for the inconvenience Excel needs to shutdown", with an error log as long as a football field stating jargon beyond my understanding.
It does not do this error on any other workbook. Version is Office 2000 ( 9.0.2720 )
I am dealing with several very large spreadsheets using VBA to do various things. I found that my code worked well, but was taking a long time to run. The biggest time consumer was my use of the AutoFilter features. I have since turned calculations to manual before my code runs and set it back to auto when my code is done running. What are the potential consequences of my turning calculations to manual and then back to auto?
I have a combobox which is used to select data (stock symbol) from a list. Following selection of the stock symbol, a VBA routine is invoked from a radio button which accesses the internet to get data for the selected stock. Currently, this is a two step operation. I tried to cut out the need to click on the download button by calling the GetData sub inside the combobox sub as shown in the code below.
Application.ScreenUpdating = False Dim s As Variant Dim resp As Long resp = MsgBox(prompt:="You are about to clear ALL current year payroll information. Are you sure you want to continue?", Buttons:=vbYesNo) If resp = vbNo Then
I just want to have a column sorted by Z-A order and then A-Z by the same button, I recorded 2 macros on 2 buttons and tried to join codes with "if, elseif".
I wanted to know If there a way to hide the process of executing macrob (vba)? I have a long macro that runs on a file and I want to hide the process. I know there is an option to it, I just don't know it.
The error trap does work when there is an error, but it also runs at the end of the code when there isn't an error... I'm not sure what I've done wrong. When I test with data that is correct, it still runs through the code when it gets to the bottom. Have I put the error trap in the wrong place?
I have put in bold the section of the code with the error trap.
Option Explicit
Const TargetDB = "Group Life Pricing Tables v0.1.accdb"
Sub HMUK_Val() Application.ScreenUpdating = False
'define variables to be used in the process Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim fld As ADODB.Field Dim sSQL As String Dim SchemeInfo As Integer Dim CurrentAge As Integer Dim CurrentRow As Integer Dim CurrentSex As String Dim Message 'check that Scheme data has already been entered For SchemeInfo = 1 To 13 If Sheets("Working Info").Cells(SchemeInfo, 2).Value = "" Then Message = MsgBox("Please ensure all Scheme information has been entered before processing member info", vbOKOnly, "Warning") Exit Sub End If Next SchemeInfo
I've a macro (I recorded) that executes a query (Oracle) and returns rows that exceeds the max (65539). How do I go about adding another sheet therefore allowing the query to continue?
I know how to do it via opening a text file and importing, but do not know how to do it with the use of a query.
I have a Worksheet_Change method in my sheet module which has suddenly begun working selectively. I have several target ranges in my worksheet that I need to watch for changes. One range of cells is all pull-down menus (a list of electrical loads through excel's validation), another two cells are looking at the name and location of an electrical panel.
When the user selects an electrical load, the worksheet_change method updates a legend of definitions. When the user enters a new name or location, the method updates similar fields elsewhere on the same sheet.
The problem is, excel has stopped executing worksheet_change when a load is chosen or location entered. It only executes when the aforementioned load and location are deleted. However, the name field triggers worksheet_change just fine!
I have attempted to put breakpoints on the worksheet_change method; Excel does not even execute the method as described above. I've tried breaking on the toggling of Application.EnableEvents (which occurs early on in the execution of worksheet_change). I currently have a global watch on Application.EnableEvents and I'm quite sure it's TRUE before I attempt to trigger a worksheet_change event.
My next step is to create a brand new workbook and copy my spreadsheet and code into it. I'll post the results of that experiment.
Need to add an auto sort code to the end of this worksheet module 2, so that after all the dates are figured, it will sort by this date, no matter how many dates there are.
Currently I have an excel form that I am working on. I just figured out how to get it to open when I open the workbook. I am wondering is there code out there that will prompt excel to auto save the worksheet that the form writes to when the "OK" button on my form is clicked. I want to be able to save the data without having to rely on the user to remember to save everytime entry is complete. if there is a code for this function, where would I place it in my code listing?
Is there a way to have excel automatically save the workbook every 5 minutes or so? I'm not talking about the backup option (tools-options-save) I mean do a hard save?
Or would this warrant a timed VBA code? If so, can someone build me one quickly? I'm kinda in a time crunch - sorry for last minute notice. Co-worker just deleted 8 hours of work bec "she didn't" know to save it....
I have created a spreadsheet using columns A to G with row one as the following headings Surname, Middle Name, First Name, Alias, Offences, Temp# and CRO#.
The thing is I tried writing a VBA code to let the Surname(column A) automatically sort in ascending order but it doesn't work.
where sorting begins in Column A with the range A2:A5000 and at the same time row one being frozen.
The link above describe a VBA code for pop-up calender. I wondering is it possible to have the pop-up calender automatically display by just clicking on a cell in column A instead of pressing Ctrl C. The dates will be only in column A
I am trying to see if it is possible to create an intentional delay when executing a macro. Basically I would like to pause the macro for 5 seconds and then continue with the rest of the code.
I am trying to add some code that will auto populate cells based on entries and list selections. In the attached spreadsheet, when information is added to cells in column 'A', the date should auto populate the cells in column 'C'. When a status of "Complete" is selected from the dropdown box in cell 'B', the date should auto populate in the cell in column "D".
The code partially works in that it will auto populate columns "C" and "D", but the code errors out every time and I have to cancel the error to continue to the next entry.
If I have 3 fields: zip code, city and state; is there a way where I can have the user of my form enter their zip code and it will populate both city and state. I have the data, I just dont know exactly how to code it.
Need vba code for excel that will auto add a formatted row when a button is pushed? I have the button made, but I need it to move down a row each time a user clicks the add line button.
