Worksheet_change Executing Selectively
Apr 4, 2007
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.
View 9 Replies
ADVERTISEMENT
Jun 11, 2009
my values within column A are separated by a dash. I'd like column B to continuously copy column A with the exception of the characters after the dash. example:
____A_____________B
12345-456_______12345
22456-333_______22456
1553665-45______15553665
How can I format cell B to constantly reproduce this result?
View 4 Replies
View Related
Aug 4, 2006
I'm trying to programatically remove select controls from a worksheet (I want to keep combobox and buttons, but remove all else). I execute the following code, but keep getting a run time error "The index into the specified collection is out of bounds" about half way through the deletes.
Dim i As Integer
Dim counter As Integer
i = ActiveSheet.Shapes.Count
For counter = 1 To i
If ActiveSheet.Shapes(counter). Name = "ComboBox1" Then Goto SkipDelete
If ActiveSheet.Shapes(counter).Name = "CommandButton1" Then Goto SkipDelete
If ActiveSheet.Shapes(counter).Name = "CommandButton2" Then Goto SkipDelete
If ActiveSheet.Shapes(counter).Name = "CommandButton3" Then Goto SkipDelete
ActiveSheet.Shapes(counter).Delete
SkipDelete:
Next counter
View 4 Replies
View Related
Jun 23, 2007
I am using this code to clear 25 sheets in a workbook.
Sheets("Dadadooo Lasino").Select
Range("A2:I500").Select
Selection.ClearContents
This code works fine, but was wondering if a faster way to do this existed.
View 5 Replies
View Related
Feb 27, 2013
I have a chart with multiple series which I am hoping to show/hide based upon a particular value attached to each series. I was therefore wondering if it was possible to extend the Series object class so that I could have something along the lines of a "Series.specialvalue" property which I could assign and then check to decide whether to show or hide the series?
View 1 Replies
View Related
Aug 18, 2009
=IF(G88="PT",VLOOKUP(B88,Vlookup1!A:R,5),IF(G88="OT",VLOOKUP(B88,Vlookup1!A:R,6),IF(G88="ST",VLOOKUP (B88,Vlookup1!A:R,7),FALSE)))
Two tabs in an excel spreadsheet. One with this formula and another one named Vlookup1 with the info (three columns indicating diagnosis codes for PT (Physical Therapy), OT (Occupational Therapy) and ST (Speech Therapy). Out of approximately 350 rows, this formula works perfectly fine with all but three customers’ names. It seems to be only with the last three customers –sorted alphabetically– and can’t figure out how to solve it. Have reduced the amount of rows in the info tab / have changed the format of all cells for these 3 customers / have retyped all information under another row and inserted above all the conflict rows but it always brings “0” as the cell value – not even False….
View 4 Replies
View Related
May 9, 2012
How do I selectively rename sheets by sheet code name?
Instead of sheet1.name = "New Name" I need sheet1 to be a variable of a specific sheet.
View 4 Replies
View Related
Feb 26, 2008
I've got a master excel sheet with about 6000 rows of unique variables. In separate source files, I have many rows of these variables with information next to each. Each file has a random number of these variables, some a couple of hundred, some a thousand or two.
What I'm looking to do is to have the master sheet with all 6000 rows and have all of the information next to each variable, with new columns for each new bit of information.
I could sit there for hours copying and pasting each new bit of information to the relevant variable in the master sheet, but I'm convinced there's a quicker way. Is there, for example, a way to filter the master sheet based on the variables contained in one of the source files and therefore (once a-z sorted) copy all the information from a source file and simply paste it into the master file? Then once the filter is lifted the same can be done for the next file (there's only about 20 source files so that's manageable).
View 7 Replies
View Related
Apr 26, 2008
Is it possible to selectively format text in a textbox on a userform ie. to make some of the text bold, other parts underlined and other parts italic etc. The text which appears in my text box is entered automatically by a piece of code which extracts the contents of certain cells and arranges them in the text box as needed. I would now like to be able to display the text box with the text formatted so that its not just all in plain text.
View 4 Replies
View Related
Jul 9, 2013
I'm trying to copy cells from a column in one workbook to cells in a column in another workbook based on what's in a header column.
Such as
Original workbook
Mat Qty
A 1
C 3
D 2
E 4
G 5
Total 15
Target workbook becomes
Mat Qty -> Mat Qty
A -> A 1
B -> B
C -> C 3
D -> D 2
E -> E 4
F -> F
G -> G 5
My code so far (which might not be worth reading) is:
Code:
Sub CopyColumnToWorkbook()
Dim sourceColumn As Range, targetColumn As Range, sourceColumnValue As Range, targetColumnValue As Range
Dim mySourceCell As Range, myTargetCell As Range
[code].....
I'm getting a runtime error ('1004'. Application-defined or object-defined error) on the Copy statement within the If sttement.
View 3 Replies
View Related
Feb 23, 2014
I am attempting to modify VBA code from [URL] .....
The section of the code I want to modify is below dealing with importing selective text. The original function returned a "False" if the line contained a keyword specified in the Array function. I have reworked it so that it shows a "True" if the line contains a keyword specified in the Array function.
My only problem is that it only does it when the keyword appears in the beginning of the line, as it uses the "Left" operator in looking at line. How do I configure the function so that it returns a "True" if the keywords appears anywhere in the line?
View 4 Replies
View Related
Jul 17, 2008
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.
View 14 Replies
View Related
Apr 30, 2013
Why the second sub is not executing?
Code:
Private Sub RolloverYear_Click()
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
[Code] .......
View 9 Replies
View Related
Oct 4, 2008
if it is possible to use a batch query to execute more than one SQL statement at once from Excel. Something like this:
View 9 Replies
View Related
Dec 17, 2008
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".
View 6 Replies
View Related
Aug 30, 2009
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.
View 5 Replies
View Related
Mar 6, 2008
I have 60 checkboxes on my spreadsheet and basically, when any of the checkboxes are clicked on, the same action should be performed.
The only way I know how to do this is if I were to write the same code 60 times... one for each checkbox_click() method.
View 9 Replies
View Related
Apr 2, 2008
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...
View 9 Replies
View Related
May 22, 2009
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
View 9 Replies
View Related
Dec 10, 2009
(Excel 2003)
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.
View 9 Replies
View Related
Mar 21, 2007
I have a series of checkboxes that I would like to select/deselect on mass, without having to manually click them individually...
Each of the CheckBox_Click routines change the visibility properties of images.
Changing the CheckBox value with code (true/false)
CheckBox.value = True
will alter the checkbox, but not execute its code...
And
Run "CheckBox_Click"
did not do it either.
How do I simulate the clicking of the CheckBox, with code?
View 3 Replies
View Related
Apr 26, 2007
What I want to do is make a simple text link inside of a cell so when it's clicked on it executes the CommandButton(x) VBA script.
I'm guessing =HYPERLINK is going to be the best method.
Like so:
=HYPERLINK("Forms.CommandButton1","E-Mail")
View 9 Replies
View Related
Jul 24, 2008
With worksheet_change(byval target as range), is there a simple way to focus on only the row that has changed? Currently I have a 'do while' loop that looks for x until it is blank. That is causing a delay that I am hoping to avoid by only changing one row.
Also, any direction on resoures that shed more light on the front half of macros (dim, range, where you want the macro to look and act, etc)
View 9 Replies
View Related
Jul 17, 2009
Can you have more then one worksheet_change event for the same worksheet and if not what is a work around. I have two that I want to use but only the first one works. Both work if they are the only one, but when they are both present the second one does not work.
View 9 Replies
View Related
Oct 6, 2009
i have some code that is set in the Worksheet_Change routine.
For some reason the code is not activated when a change is made on the relevant sheet.
I can type into boxes and hit return amongst other things and the code simply wont fire, is there a reason for this (am i missing something)?
my code is...........
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.DisplayAlerts = False
If Range("r_PatientType") Sheets("Data store").Range("store_PatientType") Then
If Range("r_PatientType") = "Paroxysmal" Then
View 9 Replies
View Related
Nov 14, 2009
What is the syntax for executing a 'SHIFT+xlUP' function in an Excel macro?
View 4 Replies
View Related
Mar 14, 2013
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.
Is such a thing possible with Excel?
View 2 Replies
View Related
Nov 20, 2007
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.
View 9 Replies
View Related
Mar 29, 2005
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.
View 9 Replies
View Related
Apr 5, 2007
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....
View 5 Replies
View Related
