Excel VBA help... Compare value.... please !

[Navad]

Hello,

I am trying to preform an action (launch some VBA code) once my defined cell value goes up.

ie... my current cell value is 2 and goes up to 3 so I want it to activate the VBA code I have written.

One problem is that the cell value is ( ='PRGN VALUE'!H26 ) which is a formula. PRGN VALUE is the worksheet and H26 is the cell which contains the value.

The data (number) which goes in the cell H26 is from a WEB QUERY i set up and is updated regularly.

Any help will be greatly appreciated ! Thanks 4 your time.

 

[Goska]

Well, here is a way to really cheat the system. Drop in a text box, and load the VBA code to it that you want to run, including IF statments to check for the 3 and all that good stuff. I reccomend just calling a subroutine from this _change command, but that is up to you. Then set the linked cell property of the text box to a cell, say A1 that has ='PRGN VALUE'!H26 in it. When the value of A1 changes, the VBA will run.

Hope this helps

 

[segmentationfault]

Give this a try.
The "'Sheet1'!A1" must refer to the cell you are trying to watch. (The one with formula ='PRGN VALUE'!H26 in your message).

Every time you open the file, the value of the cell will be stored in dblValue. Whenever the value of the cell increases, it will run "MyMacro".


Private dblValue As Double

Private Sub Workbook_Open()
mdblValue = Range("'Sheet1'!A1&quot
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
If Range("'Sheet1'!A1&quot > dblValue Then
Call MyMacro
End If

dblValue = Range("'Sheet1'!A1&quot.Value
End Sub

 

[Navad]

Thanks alot people ! I owe you big time !

 

[segmentationfault]

As usual, I included a typo just for fun. Here's the corrected sub.



Private Sub Workbook_Open()
dblValue = Range("'Sheet1'!A1&quot
End Sub

 

[Navad]

hi again !

I have tried the below code and still have had no luck. Any ideas would be very helpful. Thanks.

Private dblValue As Double

Private Sub Workbook_Open()
dblValue = Range("'PRGN VALUE'!H26&quot
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
If Range("'PRGN VALUE'!H26&quot > dblValue And Range("C7&quot.Value = "No Action" Then
Call MyMacro
End If

dblValue = Range("'PRGN VALUE'!H26&quot.Value
End Sub

 

[Navad]

hi goska...

Your method has worked just fine... how did u come up with that idea...? Thanks alot !!

segmentationfault i'm still working on your method and I greatly appreciate your help! I'm sure ive made a mess of it some where as I am still 15 an learning, but thanks 2 u I have picked up a lot !

Thanks again.

 

[segmentationfault]

It was my understanding that you had a cell with formula "='PRGN VALUE'!H26". We'll call this cell 'Sheet1'!A1. When the value of 'Sheet1'!A1 increases, you want to run MyMacro. Insert the code I provided into ThisWorkbook and you'll get exactly that.

If Range("'PRGN VALUE'!H26&quot > dblValue And Range("C7&quot.Value = "No Action" Then

Change "C7" to include the sheet, ie "'Sheet1'!C7". I'm not sure if this is what's causing your current problem, but it will certainly cause improper behavior at some time. Also, by using the 'PRGN VALUE'!H26 within this code, you are looking directly at the input to the cell with formula "='PRGN VALUE'!H26". This completely bypasses the cell with the formula. Maybe this is what you want, I just thought I'd point it out.

 

[Goska]

Wow. 15. I didn't start learning about VBA untill a few months ago. And I've only been really using Excel as more than a quick formatting/graphing tool for about a year. I wish I had started on this when I was 15.

Anyway, I came up with this method because I like to abuse and exploit everything as much as I can, and in VBA, there are no penalties (other than runtime), so I beat it as much as I can.

Every control in Excel has some kind of an OnClick or OnChange command. And I found out about the backwards linking on accident and it involved alot of swearing and throwing things at my monitor. I call this the learning process.

But you wanted a method to run only when a certain cell changed, and this is the quickest and dirtiest way to get there.

If I was going to use my method, I would set the height and width to .5 on that textBox so the user never sees it, as if the user types any information in there, it overwrites the code in the LinkedCell.

Also, I noticed that you are new to the site. As a general rule, you get much better answers to standard questions by doing a keyword search of the forum than by starting threads. If you can't get an answer, start a thread. Use the search thing in the box where you start a new thread, and it will search the current forum without any complications.

I get more answers that way than by starting threads. And almost everything I have learned about VBA is from this website. I read a book t get familiar with the syntax and structure, then fell accross this site while I was looking for information on how to actually use VBA. And before VBA, the only language I ever coded in was FORTRAN.

I am by no means an expert at this stuff, but I can make it do what I want it to. If it doesn't, I come here. There are some true experts around here, and then there are people like me that can get alot done without alot of hassle, but there are often much better ways to do things than my methods.

 

[Guest_imported]

How to call Excel in visual basic application