How To Avoid Displaying Movement Across Sheets While Executing VBA Code
Feb 5, 2014
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 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 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 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.
whenever you want to delete a sheet, excel would prompt you to ask you if you are sure you want to delete the sheet. im making a vba so that the sheets will delete upon workbook close, but i dont want to be prompted everytime to be sure to delete it when running the vba macro. Also, i would like to avoid being asked to save any changes to my workbook
I have a userform that has a control button that opens another userform that contains a listbox with a range of data. I want to click on an item and press a control button to add it to a textbox on my first userform. I do this via:
Possible to write code to hide selected sheets in an excel file depending on which user is using the file i.e. hiding sheets that are not applicable to that person while just showing the applicable sheets?
My Problem is I have a workbook with multiple sheets with a possibility of a number between 2 and 999 occurring. I am looking for a formula that can display a table on the "total" worksheet for every ID number that has been entered and the number of times the ID number is displayed.
I would like to prompt the user of my Excel Macro, with a particular message which would appear while the code is running in the background, and then dissapear once the code if finished executing.
For instance: I prompt the user "Do you want to run a new Query", Yes, No, or Cancel.
If they select yes, I would like a message appear that would advise them that "this may take some time, please be patient" and possibly give them a button that would allow them to cancel the process, all the while this process is running. Once the query is complete, the message would dissapear, or if they click on the cance button.
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 am wanting VBA language in a macro that will move the cursor a certain number of cells in a certain direction. For example, I want the cursor to move right one cell, no PARTICULAR cell, just right one cell. Is there something I can use?
Is there a way to record the mouse movements within VBA. I essentially want to use excel to open an external program and execute specific commands within that program. Unfortunaetly the extrenal program is not a micorsoft program
I've made quite a few programs I use in excel and have wondered about this for awhile.
When you run a macro that selects cells and objects off screen or on other sheets, the screen moves to those places. Is there anyway to lock the sheet so this doesn't happen and things look a bit more professional ?
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".
A good number of years ago I used a line of code at the beginning and the end of a macro to keep the spreadsheet from moving until the macro was finished. At the close it moved if a movement was necessary.
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.
Is it possible to prevent a user from being able to move a userform around the screen area (clicking and holding caption bar)? I am captureing a image and using Pixels coordinates on the capture and need the userform to stay in the center of the screen or the image will not be catptured. The StartupPosition is center of screen, tried
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 am trying to force movement between multiple drop down menus. The menus are independent of one another. I want the user to only be able to select the information in the menus.
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.
