What is wrong with this macro?

B

Brad

The intent of the macro was to force information into cell(s) once, but allow
the user to change the information if they had better numbers.

The macro - works correctly the first time - but it overrides any changes
entered by the user.

Sub Worksheet_Change(ByVal Target As Range)
Dim lookrng1 As Range
Dim lookrng2 As Range
Dim lookrng3 As Range
Dim lookrng4 As Range
Dim lookrng5 As Range
Dim key2 As String
Dim SW As String
Application.EnableEvents = True

If Left(Range("Prod1").Value, 3) = "LBD" Then
SW = "off"
ElseIf Left(Range("Prod1").Value, 3) <> "LBD" And SW <> "used" Then
SW = "on"
End If

If SW = "on" Then
Range("d17").Value = Range("c9").Value
Range("d19").Value = Range("c9").Value
SW = "used"
End If
 
M

Mike

Sub Worksheet_Change(ByVal Target As Range)
Means that anytime this sheet changes run macro
 
A

Art

Maybe try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target <> "" Then Exit Sub

-- Your code here --

End Sub
 
D

Dana DeLouis

The macro - works correctly the first time - but ...

If I am not mistaken, each time the code runs, SW is initialized to ""

Dim SW As String

When you set it later in your code...

SW = "used"

it is not remembered between calls.
 
B

Brad

You answered my question - but it leads me to another question.

There must be a way to have the Macro remember the variable.
 
D

Dana DeLouis

There must be a way to have the Macro remember the variable.

Hi. Since I'm not familiar with the project, here is just a general idea:
If you run this small demo a few times, you will see that Sw gets larger and
larger with each call.
Is this something you can use?

Sub Example_Static()
Static Sw As String
Sw = Sw & "x"
Debug.Print Sw
End Sub

It appears to me there is some type of logic error somewhere.
If I am not mistaken, it appears the code could be written as:

If Left(Range("Prod1").Value, 3) = "LBD" Then
Sw = "off"
ElseIf Left(Range("Prod1").Value, 3) <> "LBD" And Sw <> "used" Then
Sw = "on"
Range("D17").Value = Range("C9").Value
Range("D19").Value = Range("C9").Value
Sw = "used"
End If

If so, I don't see why Sw would be turned "On", and then set to "Used."
There may be other combinations missing as well.
Anyway, just a thought. :>~
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top