Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Cell value change to trigger macro worksheet event Results

Hi! I have a cell named "Question_Number" which can take any integer value
from 1 to 20.

There are several ways to change the value of this cell:
1) Typing in a new number manually
2) Using the scroll bar to increase or decrease the value
3) As a result of various macros from pressing buttons within the sheet.

I'd like to be able to trigger an event if this value alters. E.g. if it was
previously 3, and it is then increased or decreased. If the macro puts a 3
back in, or the user types 3 into the cell manually, I don't want anything to
happen.

I have already written the code for the changes I want to take place
afterwards, but don't know enough about events and coding to get the event
triggered in teh first place.

Any chance somebody could show me the code I need to put in?

Does it start as follows?...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "Question_Number" Then
Application.EnableEvents = False
If Target.Value ........
etc...
Else
etc... (do I put a "Nothing" or something like that here?)
End If
Application.EnableEvents = True
End If

Or am I completely wrong with this?

Also, how would I adapt it if I wanted the event to be triggered even if the
same value is put back in e.g. it was previously a 3, the macro gives another
3 and the event still triggers?

I'd be very grateful for some help!
Thanks, Neil

Hi! I have a cell named "Question_Number" which can take any integer value
from 1 to 20.

There are several ways to change the value of this cell:
1) Typing in a new number manually
2) Using the scroll bar to increase or decrease the value
3) As a result of various macros from pressing buttons within the sheet.

I'd like to be able to trigger an event if this value alters. E.g. if it was
previously 3, and it is then increased or decreased. If the macro puts a 3
back in, or the user types 3 into the cell manually, I don't want anything to
happen.

I have already written the code for the changes I want to take place
afterwards, but don't know enough about events and coding to get the event
triggered in teh first place.

Any chance somebody could show me the code I need to put in?

Does it start as follows?...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "Question_Number" Then
Application.EnableEvents = False
If Target.Value ........
etc...
Else
etc... (do I put a "Nothing" or something like that here?)
End If
Application.EnableEvents = True
End If

Or am I completely wrong with this?

Also, how would I adapt it if I wanted the event to be triggered even if the
same value is put back in e.g. it was previously a 3, the macro gives another
3 and the event still triggers?

I'd be very grateful for some help!
Thanks, Neil

Hi
I have a sheet named "Risk Register" and another named "Outside Residual Risk Threshold". I need an event macro on the worksheet "View code" section, so that any time a value changes in column AF on "Risk Register", the filter on rows 8:39 in "Outside Residual Risk Threshold" is refreshed. Both sheets are protected.

The code I tried in the "view code" or "Outside Residual Risk Threshold" was as below...


	VB:
	
 Worksheet_Calculate() 
    On Error Resume Next 
    If Me.FilterMode = True Then 
        With Application 
            .EnableEvents = False 
            .ScreenUpdating = False 
        End With 
         
        Me.Protect Password:="risk*123", DrawingObjects:=True, Contents:=True, Scenarios:=True _ 
        , AllowFormattingCells:=True, AllowFormattingRows:=True, UserInterfaceOnly:=True 
         'Change range and filter criteria to suit
        Me.Range("A8:N39").AutoFilter Field:=5, Criteria1:="" 
         
         
        With ActiveWorkbook 
            .CustomViews("Mine").Delete 
            .CustomViews.Add ViewName:="Mine", RowColSettings:=True 
            Me.AutoFilterMode = False 
            .CustomViews("Mine").Show 
        End With 
         
         
        With Application 
            .EnableEvents = True 
            .ScreenUpdating = True 
        End With 
    End If 
    On Error Goto 0 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I haven't set this up correctly, since everytime you alter any cell in any worksheet, including other files open in Excel at the same time, this event kicks off. Also, it always ends on the "Outside Residual Risk Threshold" sheet, when I need it to end on the sheet you started on before the event ran (generally "Risk Register", but not always).

Are you able to tell me how best to write in a target range (changes in Col AF) that would trigger the event to kick off, so that it doesn't occur at any other time? And also how to stop it ending on the "Outside Residual Risk Threshold" sheet when it's finished it's run?

Thanks in advance
Elizabeth

Real time data feeds don't appear to trigger worksheet change event.

Does anyone have any idea how I can trigger my macro when values fed in
to cells through DDE or RTD reach a threshold?

Thanks in advance

Hi,

I'm running a worksheet change event macro and I want it to only monitor certain cell changes to trigger something. If the user changes the value of a cell in column 22 but only in rows 7, 10, 13, 16 etc... (continuing in multiples of 3 to as many rows as I might have), then I'd like the event to trigger. The event that I'd like to happen is that if there is a value entered in any of these cells, the entire row along with the row above and row below the target cell are cut and pasted into a different worksheet (called 'implanted cases'). I've done this successfully in the past but only looking at single rows so don't know appropriate code to deal with my data arranged in rows of 3. So far I have this (which is adapted from code I used before):


	VB:
	
 Range) 
    Application.ScreenUpdating = False 
     
    If Target.Cells.Count > 1 Then Exit Sub 
    If Target.Column  22 Then Exit Sub 
     'Code in here to look at change only in multiples of 3 rows from row 7... 10,13,16 etc
    If Target = "Implanted" Then 
        With Target 
             'Code below only takes one row that target is on but I'd like to take the rows immediately above and below as
well as the middle row that the target value is on
            Sheets("Implanted Cases").Range("A4").EntireRow.Insert 
            ActiveCell.EntireRow.Cut Sheets("Implanted Cases").Range("A4") 
            ActiveCell.EntireRow.Delete 
        End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance for any help...

Is there an event that is triggered by changing the data in a
worksheet cell? What is it called?

I have a table that is filled in line by line (row by row). I want
the rows that have not yet been filled in with data to appear
completely blank all the way across. Several columns on the right
contain formulae that process the data entered into the left-hand
columns. I want the process of entering data into one of the
left-hand columns to trigger a macro that writes in the forumulae in
the right-hand columns. Trigger on a single cell, notheing more
complicated than that. Does such a trigger exist?

Right now I'm using an IF(ISBLANK(B13),"",[the real formula]) sort of
formula to accomplish this, but this makes the underlying calculation
of the real formula sort of "inscrutable" which I want to avoid.

The process of using cell formatting to make zero values appear blank
doesn't work because two of the columns are running subtotals and
totals that are not zero, even if the current line contains no data
yet.

Thanks for any help,

Fred Holmes

Is there an event that is triggered by changing the data in a
worksheet cell? What is it called?

I have a table that is filled in line by line (row by row). I want
the rows that have not yet been filled in with data to appear
completely blank all the way across. Several columns on the right
contain formulae that process the data entered into the left-hand
columns. I want the process of entering data into one of the
left-hand columns to trigger a macro that writes in the forumulae in
the right-hand columns. Trigger on a single cell, notheing more
complicated than that. Does such a trigger exist?

Right now I'm using an IF(ISBLANK(B13),"",[the real formula]) sort of
formula to accomplish this, but this makes the underlying calculation
of the real formula sort of "inscrutable" which I want to avoid.

The process of using cell formatting to make zero values appear blank
doesn't work because two of the columns are running subtotals and
totals that are not zero, even if the current line contains no data
yet.

Thanks for any help,

Fred Holmes

First post ever.

I have a macro (Excel 2003) which refreshes a number of pivot tables based on values in three different cells on a separate worksheet. The macro runs fine when fired independently.

I added a dropdown list to select values for each of these cells and triggered the firing of my macro using a worksheet change event. This works perfectly.

What I really want is to use three combo boxes to select the values and to trigger the macro using a combobox change event. When I try this, however the macro bombs on the third line of code. I'm pretty new to VBA and I've never tried to use the combobox change event so I may be missing something really basic.

Here's the code I'm using for the change event. (The macro name is "RefreshPivots"). I've placed the following code on the worksheet object (within the VBA editor) for the worksheet where the combo box was placed. Again the macro runs perfectly when I run it on its own which leads me to suspect that there's something wrong with my combox change event code.

Sub
Combobox1_Change()
             RefreshPivots
            End Sub
Here's a snippet of the macro code which refreshes the first of several pivot tables:

'Refresh pivots
'  This block refreshes the pivot cache of all pivot tables to deal
'   with problems where drop-down list for Page field "SPECIALTY" was getting
'   corrupted dropping items from list and adding an item "0" in the pivot table
'   Page field selection list. Code rebuilds the
'   pivots by
'       1) Removing items from the "Page" dimension of each pivot table
'       2) Refreshing the pivot cache
'       3) Adding the Page fields back
'       4) Supplying the user selected values for each of the three page fields
'
'    **********************************************************************
'    **********************************************************
'    ****                                                              ****
'    ****   1   Refresh Pivot Table:                          ****
'    ****       Filled_2_yrs_ago_Counts_by_Month   ****
'    ****       (Worksheet: Filled Reqs - Counts       ****
'    ****                                                              ****
'    **********************************************************
'    **********************************************************

'               ***************************************************
'   *************  (a) Refresh Cache
'               ***************************************************
    Sheets("Filled Reqs - Counts").Visible = True
    Sheets("Filled Reqs - Counts").Select
    
    ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("SPECIALTY"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("MED_CTR"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("RECRUITER"). _
        Orientation = xlHidden

    ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotCache.Refresh ''

    With ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("SPECIALTY")
        .Orientation = xlPageField
        .Position = 1
    End With

    With ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("MED_CTR")
        .Orientation = xlPageField
        .Position = 1
    End With

    With ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("RECRUITER")
        .Orientation = xlPageField
        .Position = 1
    End With

'               ***************************************************
'   *************  (b) Refresh Pivot with user-selected values
'               ***************************************************
   ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("SPECIALTY"). _
        CurrentPage = Range("A1").Value

   ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("MED_CTR"). _
        CurrentPage = Range("A2").Value

   ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("RECRUITER"). _
        CurrentPage = Range("A3").Value


The line of code (executable line3) where the macro fails is:
    
    ActiveSheet.PivotTables("Filled_2_yrs_ago_Counts_by_Month").PivotFields("SPECIALTY"). _
        Orientation = xlHidden
The error is:

Run-time error '1004'
Unable to get the PivotFields property of the PivotTable class

Thanks for any help you can offer

I encountered some problems while doing my excel vba. Please give some enlightenment and guidance along.

Overview of what i am doing
I have a workbook with 3 worksheets. In each worksheet, there is a web query trying to get data from a webpage. The web query is at Cell A1 of each worksheet. The query is supposed to autorefresh every 5 minutes and i will have some code running through the cells to get a few values in each sheet.

The checking code (call check) will be called every 3minutes.

If the value is greater than a threshold then pop out a message box stating the value. When this happen, the message box needs to be focus/pop up in front of the screen no matter what the user is doing (checking microsoft outlook, browsing web using Internet Explorer or other activity).

Problem I have encountered:
1) I needed to password protect the workbook in case the user accidentally remove or change the cell A1 which the data is inserted into.

But i realised that the auto refresh for the web query will not work if the sheet is protected.

thus i use the following code> unprotect each sheet in the workbook then refresh the query table
For Each Worksheet In Workbooks("Datacheck").Worksheets
Worksheet.Unprotect Password:="xxxxxxxx"

On Error Resume Next
With ActiveSheet
Range("A1").QueryTables(1).Refresh
End With
On Error GoTo 0
Next Worksheet

Qn: Is there a better way to go about refreshing the web query in a protected sheet beside the code above?

How do we give a index to the querytable (i am not too sure as i imported the data using record macro method and i believe they didnt assign an index to the webquery). QueryTables(1) refering to querytable with index 1?

Another problem> Msgbox pop up:
After I run the call check macro, when the cells value exceeded a threshold, a msgbox should pop out in front of the screen. This is very important and crucial for the msgbox to grab the attention of the user.

As this excel program will be running for 12-20 hours each day, I needed the msgbox to grab the user attention every time the cell values exceeded threshold (currently i am running the check subroutine every 3 minutes which is still sufficent timing for the user to react or immediately which is better still).

I had use the following code in my program to cause the message box to pop up in front of the screen. But there is a few occurences that the msgbox did not pop up in front of the screen (the message box did trigger behind the excel but it did not force shown in front of the other application i am running like Internet explorer, microsoft outlook and so on ). The excel icon in the taskbar did not flash also.

Method 1 (a separate subroutine)
Sub Test()
Workbooks("Datacheck.xls").Activate
Application.WindowState = xlMaximized
Application.OnTime Now + TimeSerial(0, 0, 2), "check"
AppActivate "Microsoft Excel"
AppActivate Application.Caption
End Sub

Method 2 (after the if statement that check the cell)
If error1 = True Then
Workbooks("Datacheck").Activate
AppActivate "Microsoft Excel"
AppActivate Application.Caption
End If
both Method 1 and 2 will sometimes miss out on the pop-ing of msgbox to grab user attention

Qn: I needed the message box/the workbook to be really shown/pop up in front of the user when the value exceeded threshold. If there any other reliable method to do this? Missing the message box might cause serious problem for my work.

Another problem> Checking based on timing
I need to perform checks on the cells in the worksheet. For example if
the threshold is 60 between 8 am to 8pm and threshold is 40 between 8pm till 8am

Needed to add a timing condition in the if else also. How can we do it?
If Sheets("Sheet1").Cells(total(count) - 1, 12) > 60 Then
error1 = True
End If
Qn: Currently i am using trying to let the macro check run every 3 minutes using this statement
Application.OnTime Now + TimeSerial(0, 0, 2), "check".

Is it better if i use worksheet change event for this case? If i use worksheet change event and place the call check statement in there, is it as soon as the cell value changes above threshold, the check routine will execute?

Qn: Since i am refreshing the 3 web query every 5 minutes and the check procedure is run every 3 minutes. Alot of activity is going on in this workbook. I also have other excel workbooks running doing other stuff, I realised that while working with other worksheet, it tends to be very laggy at times. I believe it's caused by the activities going on in the datacheck workbook. Any work around for this?

I am trying to write a worksheet_selectionchange macro in which the change is initiated only when certain cells are changed (B6:B9 and X9). I do not want the change to be initiated when any other cells on the worksheet change. B6:B9 and X9 are drop down boxes and the values within the drop-down boxes is variable.

Can anyone explain to me why the Worksheet Change event is occasionally called from a completely different sheet that doesnt even have the code within it, when I change the proper cells?

This probably isn't clear so I'll give an example.

I'm working on creating a macro to summarize some data, and when I try to change the value in cell C2, another worksheet attempts to open that has an event set to trigger when that cell is changed. Its odd, very annoying, as well as not acceptable....

I'm trying to create a print macro that hides worksheets with no entered values before printing the entire workbook. I have a cell on each worksheet that indicates if values have been entered, but I want the triggering event to be the click of a command button, but not to be dependent upon a changing value in any of these cells. Can anyone offer ideas or code to accomplish this?

Experts,

I am facing a situation and not able to find a solution...
Sheets are Dynamic
Cells are Dynamic

The problem...

In a sheet if you have a cell which is getting value from some where... say from some other workbook or internet....
When the value changes...say from 5--->6 so it should be considered as a sheet change event
So that i can trigger a macro in my Workbook_SheetChange() as the sheets and cells are dynamic (i mean it could be any sheet and any cell in that workbook)

or same thing goes with a "IF" condition used in any cell of worksheet.
If that condition goes from False---> True ...it does not trigger a Workbook_SheetChange() event ...Please Suggest something to find a solution for this problem....

Code:
' Triggered whenever a change happens on any worksheet in the workbook
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    Dim sht As Worksheet
    Dim shtChild As Worksheet
    Dim lngRow As Long
    Dim intCol As Integer
    Dim strValue As String
    Dim rng As Range
    Application.ScreenUpdating = True
    Application.StatusBar = False
    Set sht = sh
    
    ' Determine if this is a "live data" sheet or if it has a formula
    If SheetHasChild(sht, shtChild) Then
        UpdateChildFromLiveData shtChild, Target
    ElseIf UCase(Left(Replace(Target.Cells(1, 1).Formula, " ", ""), 12)) = "=MARKETDATA(" Then
        GetMarketData Target.Cells(1, 1)
    '//////////new
    ElseIf UCase(Left(Replace(Target.Cells(1, 1).Formula, " ", ""), 19)) = "=MARKETDATASUMMARY(" Then
        GetMarketDataSummary Target.Cells(1, 1)
    ElseIf UCase(Left(Replace(Target.Cells(1, 1).Formula, " ", ""), 11)) = "=HEADERROW(" Then
        GetHeaderRow Target.Cells(1, 1)
    ElseIf UCase(Left(Replace(Target.Cells(1, 1).Value, " ", ""), 11)) = "CONMODIFY" Then
     '   GetConModify Target.Cells(1, 1)
                    intCol = 0
                For lngRow = Target.Row To 1 Step -1
            ' stop looking if a blank cell is hit
                 If IsEmpty(sh.Cells(lngRow, Target.Column)) Then Exit For
                    If Not IsNumeric(sh.Cells(lngRow, Target.Column).Value) Then
                        strValue = sh.Cells(lngRow, Target.Column).Value & ""
        
        
                        If strValue = "" Then
                    intCol = Target.Column - 8
                ElseIf strValue = "ConModify" Then
                    intCol = Target.Column
                End If

                
                If intCol > 0 Then
                    ' check for the formula
                    If UCase(Left(Replace(sh.Cells(lngRow, intCol).Formula, " ", ""), 4)) = "=IF(" Then
                        Exit For
                    Else
                        intCol = intCol - 1
                    End If
                End If
            End If
        Next lngRow

        If intCol > 0 Then GetConModify sh, sh.Cells(lngRow, intCol), Target
        
        
        ElseIf UCase(Left(Replace(Target.Cells(1, 1).Offset(0, -1).Value, " ", ""), 11)) = "CONMODIFY" Then
     '   GetConModify Target.Cells(1, 1)
                    intCol = 0
                For lngRow = Target.Row To 1 Step -1
            ' stop looking if a blank cell is hit
                 If IsEmpty(sh.Cells(lngRow, Target.Offset(0, -1).Column)) Then Exit For
                    If Not IsNumeric(sh.Cells(lngRow, Target.Offset(0, -1).Column).Value) Then
                        strValue = sh.Cells(lngRow, Target.Offset(0, -1).Column).Value & ""
        
        
                        If strValue = "" Then
                    intCol = Target.Offset(0, -1).Column - 8
                ElseIf strValue = "ConModify" Then
                    intCol = Target.Offset(0, -1).Column
                End If

                
                If intCol > 0 Then
                    ' check for the formula
                    If UCase(Left(Replace(sh.Cells(lngRow, intCol).Formula, " ", ""), 4)) = "=IF(" Then
                        Exit For
                    Else
                        intCol = intCol - 1
                    End If
                End If
            End If
        Next lngRow

        If intCol > 0 Then GetConModify sh, sh.Cells(lngRow, intCol), Target.Offset(0, -1)
        
        
        
        
    ElseIf UCase(Left(Replace(Target.Cells(1, 1).Formula, " ", ""), 14)) = "=TRADEHISTORY(" Then
        GetTradeHistory Target.Cells(1, 1)
    ElseIf UCase(Left(Replace(Target.Cells(1, 1).Formula, " ", ""), 16)) = "=MYTRADEHISTORY(" Then
        GetMyTradeHistory Target.Cells(1, 1)
    ElseIf UCase(Left(Replace(Target.Cells(1, 1).Formula, " ", ""), 10)) = "=MYORDERS(" Then
        GetMyOrders Target.Cells(1, 1)
    ElseIf UCase(Left(Replace(Target.Cells(1, 1).Formula, " ", ""), 8)) = "=CREATE(" Then
        MakeCreateTable Target.Cells(1, 1)
  End If
Set sht = Nothing
    Set shtChild = Nothing
End Sub

Any Help is greatly appreciated !

Regards & Thanks.

Good evening all, Yesterday I asked a question about automating a macro when switching from one worksheet to another. Erik van Geit kindly offered me the advice to use a "deactivate" code in the worksheet. Unfortunately, because of the way I have written the macro, using this code results in the programme getting in to a loop.

If I can explain further: The macro copies and pastes a row of cells from worksheet 1 to worksheet 2. However, using the "deactivate" event trigger, the macro does not start until worksheet 1 is closed, i.e. worksheet 2 is opened. At that point the macro returns to worksheet 1 to copy the cells. It then returns to worksheet 2 to paste the cells, but in doing so it closes worksheet1 and the macro kicks in again........ ad infinitum!!

Can I add a further element to the deactivate event code, so that the macro only runs when leaving worksheet 1 if there has been a change. If this is possible, then the macro would not be triggered for a second time when it leaves worksheet 1 after copying the cells.

I suppose another approach would be to have the macro run whenever a cell is changed in worksheet 1. I have seen the "Change(ByVal Target As Range)" event. This works if a value changes. Is there a similar command that triggers the macro if the format of a cell changes. e.g. its colour?

I know there are a few different questions here, but hope someone can advise.

Thanks, Dave

I initially posted a thread on "How do get a macro to autorun when i change a cell in the spreadsheet?"

Ok, here come the problem now, I replicated this worksheet in the same workbook to do another analysis. Now i have 2 worksheets namely CountryA and CountryB (Copied via using create copy of worksheet).

So when I change the cell in CountryA, the macro is also running CountryB. The Cell which I change is actually a Validation list of "Purchase" and "Sales". I change it let's say from Purchase to Sales. It goes to the CountryB worksheet and runs the macro???

Why is this Private Sub Worksheet_Change(ByVal Target As Range) also performing the macro on CountryB worksheet?

How can make sure that it is only running for CountryA when I change the cell in this worksheet?

Is it something wrong i did?

Please help... and Thanks in advance

Below was the reply that i have got previously

PHP Code: 
 Question : Let say Cell "A1" has only 2 values that is Purchase or Sales.

Now when i change this cell to either value, i want it to run a macro
e.g. When i change Cell to "Purchase" i want to autorun Macro A and When i change Cell to "Sales" i want to autorun Macro B. How do i do this? And it is for a particular worksheet only.

[B]Jim Thomlinson [/B]
Right click the tab of the sheet containing the ceel with purchase and sale
in Cell A1 then select view code... Paste the following.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value = "Purchase" Then Call Macro1
If Target.Value = "Sales" Then Call Macro2
End If
End Sub

[B]Mudraker[/B]
You need to use the worksheet change event
This goes in the module for the particular worksheet that you want to trigger the macro

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
Application.EnableEvents = False
Select Case Target.Value
Case "Purchase"
Call macroA
Case "Sales"
Call MacroB
End Select
End If
Application.EnableEvents = True
End Sub 


Hey everyone,

I have a set of code that runs on the double-click event on a worksheet, to
display a user form to assist the user with entering data into the worksheet.
The user form does some data validation and then writes the values entered in
the form into the worksheet via VBA code.

The VBA code Unprotects the sheet, and re-protects before and after entering
the data. The ActiveSheet.Protect command is the very last line of code in
the subroutine before End Sub.

One user (and only one) of the sheet is getting the standard Microsoft Excel
error "The cell or chart you are trying to change is protected and therefore
read-only." after the code runs. This is despite the fact that the code has
already written all values to the sheet, and has re-protected as it's last
action before End Sub. The user is not triggering any other events which
would account for this error, and I don't know why Excel is giving the error,
nor can i work out what it is that is trying to change the data in the
worksheet.

I have tried it on their computer, stepping through my code in break mode,
and the error only comes up after the VBA has run and reached End Sub. There
are no events triggered by the user other than the initial double-click which
starts the code, and no other macros/code/events which are triggered in any
way by the user or my code.

Has anyone experienced this, and knows how to get around it?

Thanks,
Wayne

I have a document that calculates the cost of recipes based on data entered by the user from a list. Once a recipe total is calcuated, it sends that value to another worksheet, where they are all listed. This sheet is hidden and protected. Each month, the chefs will update the cost of the ingredients, which will in turn update the cost of all the recipes, which will, in turn, update the cost of the recipe on the hidden page. What I'm looking to do is make a notation (change the color of the cell, something similar) if a recipe cost changes on the hidden page. Then, after I update the POS system with the new recipe costs, clear the triggered event from the screen and be ready to start anew the next month. This is because some recipes will change and some won't.

Does anyone have an easy way to do this? I found some VBA that worked only on the worksheet, it didn't do anything when the value changed as a result of a formula (as in =Recipe!A1).

Thanks for your help.

Hi.

In column "A" I'm scriving values, for example prices.

I would like to do it faster. I found VBA code (unfortunately don't know author) which helps me much.

I have list of prices, for example 2,99 3,99 etc.

When I scrive "2" or "3" and hit enter, vba automatically insert "2,99" "3,99"

Problem is when I have prices for example 2,99 and 22,99.

I would like to scrive 2,9 or 22,9 and let vba to fill rest but doesn't recognize ","

Is there any solution or other code which will serve it?
Private Sub Worksheet_Change(ByVal Target As Range)
'Sub "autocompletes" data entered into column A using a source table on a different worksheet. If more than one match is
' found, the user is allowed to continue entering characters until a unique match is found. If no matches are found, the
' data is accepted as entered. ALT + Enter, Enter to force the macro to accept data as entered. The sub is triggered by
' the Enter key.
Dim cel As Range
Dim match1 As Range
Dim match2 As Range
Dim rg As Range
Dim targ As Range

'***Please adjust the next two statements before using this code!***
Set targ = Intersect(Target, Range("A:A")) 'Watch the cells in column A
Set rg = Worksheets("Source data").Range("B:B") 'Use named range AutoCompleteText for "autocomplete" info

If targ Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error GoTo errhandler 'If code encounters an error, turn events back on

For Each cel In targ
If Not IsError(cel) Then
If cel <> "" And Right(cel, 3) <> Chr(10) Then
Set match1 = Nothing
Set match1 = rg.Find(cel & "*", lookat:=xlWhole, MatchCase:=False) 'Match is case insensitive
If Not match1 Is Nothing Then
Set match2 = rg.FindNext(after:=match1)
If match2.Address = match1.Address Then 'Code is fooled by identical strings in two cells
cel = match1 'Only one match found. Use it to "autocomplete" the cell
Else 'More than one match found. User must enter more data. Return to "Edit" mode
cel.Activate
'Application.SendKeys ("{F2}") 'Begin editing after last character entered
End If
Else 'No matches found. Do not change entered text
End If
Else 'Strip the line feed from the end of the text string
If cel <> "" And Right(cel, 3) = Chr(10) Then cel = Left(cel, Len(cel) - 1)
End If
End If
Next cel

errhandler: Application.EnableEvents = True
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Hi,

I am writing a macro which will be assigned to many Combo boxes. I want to get the cell address of the combo box triggering the macro. The pseudo code for the macro goes here.

Public Sub Comboboxchange()
       Dim Callingcell As Range

        CallingCell = MagicFunction.ReturnCallingCell                         

       If Callingcell.Offset(0,1) <> 5    Then               
              Call Another Macro
        Else Call another Macro
       End If
End Sub
It would be great if Some one could tell if it is possible to get the cell address of the calling combo box. And how to run specific functions for each combo list item selected.

For instance

Item1 selected : macro1()
Item2 selected : macro2()
.
.
.

I also tried an alternate approach in the worksheet_change() event.
I ran the Macro from the worksheet change event when I was triggering the macro if the a cell value is say 0. The value of this cell is set by the combobox list. Due to some reason the worksheet_change event didn't work the cells changed by combo box list, but worked with other cells which were manually changed.

So after researching it looks like I may have the wrong workbook event, but am unsure how to change the code to accomodate a different event.

Right now I have a 'Status' field in column H that is derived by comparing today's date to dates in columns E & F. The macro below is setup to move 'Completed' items to the 'Completed' sheet, and move all other items ('Planned', 'Unplanned','In Progress') to the 'Current State' sheet. Unfortunately, in order to trigger the macro I have to double click on the cell in column H and hit enter.

I'm actually wanting the macro to kickoff whenever the value in column H changes (even if it is the result of a change to column E or F. I'm thinking maybe I should have the WorksheetCalculate event, but as mentioned above.

I'd also like to autosort the data as the current code moves the active row to the bottom anytime the macro is triggered.

In advance, I very much appreciate the time.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    '   Code goes in the Worksheet specific module
    Dim rng As Range
        '   Set Target Range
        Set rng = Target.Parent.Range("H4:H500")
            '   Only look at single cell change
            If Target.Count > 1 Then Exit Sub
            '   Only look at that range
            If Intersect(Target, rng) Is Nothing Then Exit Sub
            '   Action if Condition(s) are met
            Select Case Target.Text
                Case "COMPLETED"
                    Target.EntireRow.Cut Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Offset(0)
                Case "UNPLANNED"
                    Target.EntireRow.Cut Sheets("Current State").Cells(Rows.Count,
"A").End(xlUp).Offset(1)
                Case "PLANNED"
                    Target.EntireRow.Cut Sheets("Current State").Cells(Rows.Count,
"A").End(xlUp).Offset(1)
                Case "IN PROGRESS"
                    Target.EntireRow.Cut Sheets("Current State").Cells(Rows.Count,
"A").End(xlUp).Offset(1)
            End Select
End Sub
Thanks for the info


No luck finding an answer? You could always try Google.