call model doesn't work

A

afasseeh

New Member
Joined
Jul 1, 2014
Messages
13
windows 8.0
office 2013
samsung ativ 500t

the macro (Yes_Utility_Decrement) works fine when called from any other Sub either private or not. but in that particular piece of code it does not work.

Private Sub Worksheet_Change(ByVal Target As Range)


'apply utility decrement upon changing value
If Target.Address = "$D$40" Then
If Range("D40") = Range("E39") Then

Yes_Utility_Decrement
Else: If Range("D40") = Range("E40") _
Then No_Utility_Decrement
End If
End If

'apply currency upon changing value
If Target.Address = "$D$35" Then
Call Currency_Change
End If


'apply DSA max and min upon changing value
If Target.Address = "$D$30" Or Target.Address = "$D$31" Then
Call DSAMaxMin
End If

End sub

this piece of code doesn't work although when I insert a msgbox"Hi" after then it displays the message box..
Also
'apply currency upon changing value
and 'apply DSA max and min upon changing value works fine.


this code below works
Sub test()
Dim x As Double
x = 1
If x = "1" Then
If x = "1" Then
Yes_Utility_Decrement
End If
End If




End Sub
as well as the same using
No_Utility_Decrement

I tried using
Call Yes_Utility_Decrement
Run ("Yes_Utility_Decrement")

still did not work
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Andrew Poulsom said:
What is Yes_Utility_Decrement exactly and in what way doesn't it work?
Click to expand...
it is a macro (Sub) located in another module.
it changes the value of certain cells when i run it, but it does not do any change when run inside this code..
it do the intended effect when run inside any other macro as illustrated above.
moreover the are similar cases in the same macro that works extremely fine,.
 
Upvote 0
Andrew Poulsom said:
Can we see the macro please?
Click to expand...
here you are
Sub Yes_Utility_Decrement()


Dim i As Double
Dim Cformula As String
For i = 1 To 2000
If Left(Cells(i, 2).Value, 2) = "u_" Then
If Left(Cells(i, 4).Formula, 3) <> "=1-" Then
Cformula = Sheets("PSA Settings").Cells(i, 4).Formula
Sheets("PSA Settings").Cells(i, 4).Formula = "=1-" & Right$(Cformula, Len(Cformula) - 1)
Else
End If
If Left(Cells(i, 12).Formula, 3) <> "=1-" Then
Cformula = Sheets("PSA Settings").Cells(i, 12).Formula
Sheets("PSA Settings").Cells(i, 12).Formula = "=1-" & Right$(Cformula, Len(Cformula) - 1)
Else
End If


End If
Next




End Sub

Sub No_Utility_Decrement()


Dim i As Double
Dim Cformula As String
For i = 1 To 2000
If Left(Cells(i, 2).Value, 2) = "u_" Then
If Left(Cells(i, 4).Formula, 3) = "=1-" Then
Cformula = Sheets("PSA Settings").Cells(i, 4).Formula
Sheets("PSA Settings").Cells(i, 4).Formula = "=" & Right$(Cformula, Len(Cformula) - 3)
Else
End If
If Left(Cells(i, 12).Formula, 3) = "=1-" Then
Cformula = Sheets("PSA Settings").Cells(i, 12).Formula
Sheets("PSA Settings").Cells(i, 12).Formula = "=" & Right$(Cformula, Len(Cformula) - 3)
Else
End If


End If
Next




End Sub
 
Upvote 0
Andrew Poulsom said:
Maybe this test is failing:

Code: 
If Range("D40") = Range("E39") Then
Click to expand...

No
because the following code show the 2 msgboxes hi1 and hi2
If Target.Address = "$D$40" Then
If Range("D40") = Range("E39") Then

Yes_Utility_Decrement
Else: If Range("D40") = Range("E40") _
Then msgbox"hi1"
No_Utility_Decrement
msgbox"hi2"
End If
End If
 
Upvote 0
This worked for me:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$40" Then
        If Range("D40") = Range("E39") Then
            Yes_Utility_Decrement
        End If
    End If
End Sub

Sub Yes_Utility_Decrement()
    MsgBox "Yes_Utility"
End Sub

Maybe your problem is that you have unqualified calls to the Cells property and the wrong worksheet is active.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Code not working - what is wrong.
2 3
Replies
23
Views
667
My Aswer Is This
My Aswer Is This
D
  • Question
F8 Has Stopped working for me
Replies
5
Views
540
Doug Mac
D
L
  • Question
VBA code not working on Mac
Replies
7
Views
523
Jim Gordon
J
L
  • Question
VBA Code to paste special values over a cell depending on another cells value....
Replies
1
Views
617
Micron
M
R
  • Question
Issue With VBA Code Stopped Working
2
Replies
16
Views
741
RegularExcelUser
R

Forum statistics

Threads
1,214,376
Messages
6,119,179
Members
448,871
Latest member
hengshankouniuniu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top