Excel2003: Screen Updating Not Working For Worksheet_SelectionChange When Another Sub Called
May 19, 2009
Excel 2003, Windows XP SP3
I cannot solve problem 1, thus I tried workarounds, however there are also problems with them (problem 2, problem 3). I do not use .activate or .select in my code. I have recreated the problem in sample workbook.
Problem 1:
I get a blink when I call another sub from Worksheet_SelectionChange event.
Event is located in Sheet module, CellChange procedure - in seperate module
Sample ...
View 9 Replies
ADVERTISEMENT
Feb 3, 2014
I am using this macro to open a selected folder and apply a macro to all the file in the folder. However, I don't want the user to see what is the loaded file doing. I mean, all the selected file will be formatted by the calling macro in minimize way.
View 7 Replies
View Related
Sep 19, 2008
I have popped the code below in the worksheet and it will not work on my machine (Vista + Office 2003). However, it works fine on my colleagues machine (XP + 2003).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 4 Then GoTo usevalue
On Error Resume Next 'MUST reenable events...
Application.EnableEvents = False
ActiveCell.Offset(1, 1 - Target.Column).Select
Application.EnableEvents = True
usevalue:
ActiveCell.Copy 'Put value into clipboard (marching ants)
ActiveCell.Interior.ColorIndex = 36
End Sub
I have tried different code from different web sources on different fresh files. This includes the Worksheet_Activate() function.
My macro security setting are low.
View 9 Replies
View Related
Feb 13, 2009
Is there a way to disable updating to the screen but have a message displayed to the user while a macro is running? Something like a userform displayed on the screen or a message in the status bar. I vaguley remember you could "print" message to the status bar in LotusScript, wondering if there is something similar in Excel.
Application.ScreenUpdating = False
"the macro is running, hang tight"
...macro code in here...
Application.ScreenUpdating = True
View 9 Replies
View Related
Sep 7, 2007
I have a personal.xls macro that basically does a comparison of two different workbooks. One of these workbooks ihas it's own macros (which are there in case the user wants to run them separately)...anyways my main macro calls these other macros one at a time.
It seems that at each call, the screen updates, alternating sheets and/or workbooks....
I know about the Application.Screenupdating=True / False statements....but I am not sure where I should be putting them to get the whole thing to work seamlessly.... I tried them at the start and end of my main macro, but that doesn't do the job.
View 14 Replies
View Related
Nov 1, 2008
i have a code (Below) using icolor that changes the colour of the cell depending on critiria, the problem i have is when i input into the sheet using a drop down box the color remains the same, without a drop down box it works but i really need to to work with the drop down boxes,
View 14 Replies
View Related
Jun 20, 2013
I have a macro that opens many excel documents. I used the application.screenupdating to turn off viewing these excel documents opening unfortunately they are still viewable. I counted the number of trues and falses in the module and there are 4. I believe the 2 pairs are unrelated. I keep both subs in the same module since they share functions.
Code:
Application.ScreenUpdating = False 'turbospeed
Debug.Print Application.ScreenUpdating 'shows true in break mode
View 9 Replies
View Related
Jul 24, 2008
I have some code that opens several different spreadsheets, copies data and pastes it to the main sheet (that contains the code)
I have added the Application.screenupdating = False line of code, but it still does not seem to freeze the screen.
View 9 Replies
View Related
Feb 23, 2010
The following short macro moves data into an area from where a series of graphs can either be displayed on the screen or printed. It may not be the most elegant of code and the way in which I "jump" around the hard copy option may raise some eyebrows, however except for what follows, it generally works.
When the "S"creen option is taken only the first graph is displayed, subsequent ones are not shown, (the screen does not refresh). What do I need to add to the coding to ensure each graph is shown? .....
View 9 Replies
View Related
Mar 28, 2009
I have written a user-defined function that searches for a small range within a larger range. The function requires two input parameters: the range you are looking for, and the range you want to look within.
For example, I might look for the string of values in cells A1:D1 in a larger range E1:H20. The function returns the row number in the larger range where the smaller range is found.
My problem is this: The function is working fine when I call it from another sub procedure. However, when I try to run it as a worksheet function, I get a "#VALUE!" error. The function pops up in the "insert function" menu, and it prompts for the two input parameters.
View 6 Replies
View Related
Oct 30, 2009
Using the following to open a sheet
Sub Sheet1Print()
Windows("RS1.xls").Activate
Workbooks.Open Filename:=ActiveWorkbook.Path & "" & "Sheet 1.xls"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Windows("Sheet 1.xls").Activate
ActiveWindow.Close
End Sub
I still however get a promt asking me if I want to update, and a message if i want to save the changes when I close
How do I turn of these alerts.
View 9 Replies
View Related
Dec 18, 2009
I have now seen a few codes, both from my instructor and on the web that is using code like this;
Application.ScreenUpdating = False
...... CODE HERE .....
Application.ScreenUpdating = False
Would there be any reason to this madness. Can't see why you wouldn't set it back to true and absolutly no reason to double false it?
View 9 Replies
View Related
Apr 27, 2007
I need a method of updating a set of data held on tab1 from tab2 where details of one record is shown using VLOOKUP, possibly via a macro? For example:
Sheet 1 holds info on various companies - (Column headers: Name, address1,
address2, postcode, " DATE LETTER SENT")
Sheet 2 Uses a drop down menu to select the desired company and some VLOOKUP
formulas show the info from sheet1 on that one particular company. Sheet 3 There is a printable standard template letter which draws info from the record selected on sheet 2 (using standard =Sheet1!A1 formula) formated so the letter is addressed to the specific company selected. Back to: Sheet 2 - At the bottom there is a button which says 'Print' This runs a macro which selects sheet3, prints it then returns to sheet2. I need this macro to also go back to sheet1 and update the relevant record with ideally todays date (the date the letter was printed) in the "DATE
LETTER SENT" column mentioned before but can just be an 'X' to show a letter
has been printed for this company/record at some point. So in the future, by looking at sheet1 you can easil determine which companys have had letters printed against, and which havn't.
View 2 Replies
View Related
Dec 6, 2006
i was wondering if there is a way to display a pop up message that a stating that the spreadsheet is currently updating.
What i have is a macro running on Auto_Open when workbook is opened. I have switch off the screen updating. What i want now is for maybe to have a pop up stating the spreadsheet is updating. Setting screen updating to false means that my excel freezes for a few seconds. I think users might be thinking that excel has crashed and therefore would like to include the message that the spreadsheet is updating its information.
View 9 Replies
View Related
Nov 12, 2005
I have just upgraded from excel 2000 to excel 2003 I have always set autosave to 5 minutes in case I press the wrong button as I did tonight. I also had autosave set to save all open workbooks
I am a heavy user of excel and often have many workbooks open at the one time.
View 12 Replies
View Related
Aug 20, 2013
I am trying to write an SQL query using parameters in an Excel Worksheet and then return the results to the current sheet ar cell B11. I have found it imposible to make it work in SQL so am trying to run it from VBA to pass the parameters. These are named cells StartDate and EndDate in cells A2 & A3 respectively and should replace the constant dates below. I am working in Excel2003 & MS SQL 2012.
I have tested the query on SQL server with fixed values (which are still in it). It works fine & I have pasted it in.
I took an existing query I found & tried to modify it for Excel2003 but ... Whatever syntax I use, I get a Run-time error 424 when I get to a line with 'connection'
Code:
Sub DateQuery()
With ActiveWorksheet.connections("ndserver01").oledbconnection
' .Connection = "OLEDB; DSN=Excel Files; provider=SQLOLEDB; Catalog=NDM_Sage200;Data Source=ndserver01"
.Refresh BackgroundQuery:=True
.CommandType = xlCmdSql
[Code]...
What have I done wrong
View 1 Replies
View Related
Dec 2, 2009
I need "PC" to show up if I type a sentence containing "Called Parent", "Called Dad", or "Called Mon". Here is what I'm trying.
View 2 Replies
View Related
Jul 4, 2006
I have written the following code which will use "U10" to divide by 12, & put the results on "H10.S10" if "U10 is not empty.
The results work fine except that if I delete the value in "U10", the formula "= sum($U$10/12)" will remain in H10.S10, if there any way that this formula do not remain in H10.S10?
Because this code should work from row 10 to row 47, like to ask how to write in short vba code instead of repeating the code with the need to change the row number?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("U10").value <> "" Then _
Range("H10.S10").value = "=sum($U$10/12)"
End Sub
View 9 Replies
View Related
Apr 27, 2007
Is it possible to have two Worksheet_SelectionChange events for the same worksheet if so how. If not what options do i have?
View 3 Replies
View Related
Jan 6, 2008
I found this code on one of my many searches, that works great.
(can't remember where I got it or who wrote it, (My deepest apoligies to the author))
Public pRule
Sub butRulerToggle_Click()
pRule = Not pRule
Selection.Select
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If pRule Then
For Each aCell In ActiveSheet.UsedRange
If aCell.Interior.ColorIndex = 27 Then aCell.Interior.ColorIndex = xlNone
Next
On Error Resume Next
For Each aCell In Application.Intersect(ActiveCell.EntireRow.Cells, ActiveSheet.UsedRange)
If aCell.Interior.ColorIndex = xlNone Then aCell.Interior.ColorIndex = 27
Next
End If
End Sub
My question:
This works in a sheet module. How can I put it in the This Workbook module so it works on all sheets.
By the way, what this does is highlight the whole row on a clik of a cell, leaving any color formating that was initially there alone.
Very useful if you are looking at say A10 and then want to look at Z10 without losing focus on the row.
View 9 Replies
View Related
Nov 21, 2003
Worksheet_SelectionChange(ByVal Target As Range)
from executiing each time i like to execute
Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
View 9 Replies
View Related
Apr 27, 2007
I have this code that will update column "AW8:AW60" when a date is entered into"z8:z60" if column"c8:c60" equals 4. It works great if the c column doesnt change if it does it doesnt recaluate. What I need it to do is if the number in "c8:c60" changes it recaulates.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim msgg As String
Dim msgg2 As String
Dim NewDate1 As Date
Dim c1 As Range
If Not Intersect(Target, Range("z8:z60")) Is Nothing Then
msgg = InputBox("Please Enter A Date..." & vbLf & vbLf & "(MM/DD/YY)")
If msgg = vbNullString Or Not IsDate(msgg) Then
MsgBox "You Did Not Enter Date!", vbCritical
With Target
View 7 Replies
View Related
Jun 12, 2006
When the next empty cell in column A is selected, add a formula in the same row, column H. I'm not sure on how to hard code this and used the offset instead. So maybe instead of offsetting, i should/could change the references? However, as it is, it also adds the formla in column H when column G is selected and data is entered. I cant recreate this with any other column though.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A5:A65000")) Is Nothing Then
If ActiveCell.Offset(-1, 0) = " r " Then
If ActiveCell.Offset(0, 7) = "" Then
ActiveCell.Offset(0, 7).Formula = "=if(RC[-1]="""","""",RC[-1]+120)"
Exit Sub
End If
End If
If ActiveCell.Offset(-1, 0) = "" Then
MsgBox "Please select the next empty cell/row!"
Exit Sub
End If
If ActiveCell.Offset(0, 7) = "" Then
ActiveCell.Offset(0, 7).Formula = "=if(RC[-1]="""","""",RC[-1]+120)"
End If
End If
End Sub
View 2 Replies
View Related
Jul 13, 2007
The attached file receives information from another program in cells A2:D2. It then carries out a copy/pastespecial, and then does a copy insert. The script is supposed to update when new data enters the cell, however it keep adding an additional null line. Does anyone know why this is or how to fix it?
View 2 Replies
View Related
Aug 22, 2008
I have some code which enables new comments to be added when a user double clicks a cell, but I want to restrict this to a specific range B5:B125. How do I change the code to reflect this, and add the current Date to new comments added. Here is the first section of the code which sets the range etc...
Option Explicit
Public oldRange As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
Dim rng As Range
Set rng = Target(1, 1)
oldRange.Comment.Visible = False
With rng
If Not .Comment Is Nothing Then
If .Comment.Visible = False Then
.Comment.Visible = True
Else
.Comment.Visible = False
End If
End If
End With
Set oldRange = Target(1, 1)
End Sub....
View 4 Replies
View Related
Dec 29, 2009
I'm just looking to prevent users from using the print screen or the alt print screen buttons on the keyboard. I have this script that works if I use "39" (Right Arrow)but wont work if I use "42" (Print Screen Button).
View 5 Replies
View Related
Feb 23, 2007
I have a macro called "Copying" and this runs perfectly well when run on its' own.
I have now created the following peice of
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("form").Range("B14").Value x Then
x = Sheets("form").Range("B14").Value
End If
Application.Run "Copying"
End Sub
Which (should) run the "Copying" macro if cell B14 is changed........here is my problem.
It seems to run this macro over and over again. How can I get it to just run it once?
View 9 Replies
View Related
Jan 9, 2008
I'm using the calander control in Excel which is called in VBA when a user clicks on a cell in a range - the code is as follows:
' Calander for Activity Start Date and end dates
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, [H11:I30,H33:I52,H55:I74,H77:I96,H99:I118,H121:I140,H143:I162,H165:I184,H187:I206,H209:I228,H231:I250,H253:I272,H275:I294 ,H297:I316,H319:I338,H341:I360]) Is Nothing Then
If Intersect(Target, [K11:L30,K33:L52,K55:L74,K77:L96,K99:L118,K121:L140,K143:L162,K165:L184,K187:L206,K209:L228,K231:L250,K253:L272,K275:L294 ,K297:L316,K319:L338,K341:L360]) Is Nothing Then
Calendar1.Visible = False
Exit Sub
End If
End If...........
View 9 Replies
View Related
May 12, 2006
1. Can a Subroutine be called from within a user-defined Public Function? How?
2. Is there a difference between calling a subroutine with a 'Call Sub_Name' statement, vs. calling the subroutine with an 'Application.Run'(?? or similar) statement?
View 5 Replies
View Related
May 21, 2009
If I have several Forms check boxes assigned to the same procedure, is it possible to tell which check box called the procedure?
View 6 Replies
View Related
