UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> On Change (excel), Office 2010    
 
   
bakersburg9
post May 15 2018, 01:17 PM
Post#1



Posts: 5,129
Joined: 2-November 04
From: Downey, CA


I got this code from Microsoft's site, and it doesn't work - I want to run a proc when the value in cell K2 changes - when I was just TESTING this macro, it wouldn't work - no error, just nothing happens

CODE
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    Set KeyCells = Range("K2")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        MsgBox "Cell " & Target.Address & " K2 has changed"
      
      
    End If
End Sub
Any ideas ?

Edit - the MsgBox line may not be right, but I tried it with an actual sub-routine, and it still didn't work
This post has been edited by bakersburg9: May 15 2018, 01:20 PM
Attached File(s)
Attached File  worksheetChange.zip ( 11.27K )Number of downloads: 1
 
Go to the top of the page
 
DanielPineault
post May 15 2018, 01:33 PM
Post#2


UtterAccess VIP
Posts: 5,914
Joined: 30-June 11



Take a look at the attached, it has to do with where you created your code.
Attached File(s)
Attached File  worksheetChange.zip ( 11.55K )Number of downloads: 1
 

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
bakersburg9
post May 15 2018, 02:55 PM
Post#3



Posts: 5,129
Joined: 2-November 04
From: Downey, CA


Daniel,
Are you saying the code has to reside in "ThisWorkbook" of the active workbook, and not a module ? The Sub has to run in a workbook that is open temporarily, so that won't work - is there any other way ? I tried it in a workbook that is always open, but that didn't work...
Go to the top of the page
 
DanielPineault
post May 15 2018, 07:35 PM
Post#4


UtterAccess VIP
Posts: 5,914
Joined: 30-June 11



You placed your code in a Module (you created) and it needs to be within the sheet itself the code applies to.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
bakersburg9
post May 16 2018, 10:15 AM
Post#5



Posts: 5,129
Joined: 2-November 04
From: Downey, CA


Thanks - I wish there was some type of work-around, because that's not feasible - I believe you, but I'm surprised there's no way it can be done - I need to put my thinking cap on . . . what's happening is, when the user inputs a number in that cell, another proc is ready to fire off - but I don't want them to have to run the macro - there's already 3 subs they have to run - I'm trying to make it one, basically - have #2 and #3 fire off programmatically...


Edit:
I found this on mr. excel, but it was over my head - I did learn that it possibly can be done, but is too complilcated for me - I did learn you have to set up "Microsoft Visual Basic for Applications Extensibility 5.3" reference
CODE
Dim conn As Object 'ADODB.Connection
Dim rs As Object ' ADODB.Recordset
Dim ozConnStr As String
Dim SQL As String
Dim Amt As Long
Dim Item As String

If Target = Range("K2") Then
   Item = Range("K2")

     Set cn = ozConnection()
     Set rs = New ADODB.Recordset

SQL = "SELECT AmountExcl, ItemDescr FROM Items WHERE ItemDescr = " & ToSql(Item) & ""
Set rs = CreateObject("ADODB.Recordset")
rs.Open SQL, conn

If Not rs.EOF Then
Amt = rs!AmountExcl
End If

Range("C22") = Amt
End If


Appreciate your input ... cool.gif

Steve
This post has been edited by bakersburg9: May 16 2018, 10:32 AM
Go to the top of the page
 
DanielPineault
post May 16 2018, 12:07 PM
Post#6


UtterAccess VIP
Posts: 5,914
Joined: 30-June 11



That code will still need to be trigger by an event in the worksheet, what you do from there is up to you.




I'm still not getting to big picture though. What's the issue with what I supplied you exactly? You can execute as many Procs from there as you need.



--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th May 2018 - 05:12 AM