make insert statement in VBA Excel

Question

i'm learning VBA Excel and i have a problem with my project

Here the code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim score As String, result As String
score = Range("A1").Value

If score = "1" Then
   result = "book1"
ElseIf score = "2" Then
   result = "book2"
Else
   result = "wrong"
End If

Range("A1").Value = result
End Sub

i try to make the insert statement , when i insert "1" in "A1" then the result is "book1"

this is when the value is "1"

this is after i click enter button and the result is "book1"

and this is the result when i click enter button once more

it happen because when i click the first enter , the system read value is "1" so system return "book1", but when second enter the system read the value is "book1" and return "Wrong"

so how can i do to make the system only ready the first value every i enter or move cell

Thanks guys , sory for my bad english

Maybe add logic to don't do anything if the value is book1 or book2 already? — BigBen
– BigBen, Commented Feb 4, 2020 at 4:18
@BigBen it work when i only have 2 value , and no way i use that when i have so many value , maybe you have another suggestion ? — Riky Andreas
– Riky Andreas, Commented Feb 4, 2020 at 4:24
Use a lookup table or maybe a dictionary and then you can see if the cell's value should not be changed by comparing. — BigBen
– BigBen, Commented Feb 4, 2020 at 4:30
Use Choose, or two arrays if you talking many more values. Or simply exit sub when A1 is not numeric. — JvdV
– JvdV, Commented Feb 4, 2020 at 7:13

Naveen Kumar · Accepted Answer · 2020-02-04 05:05:27Z

2

You can add one more condition with OR operator for book1 & book2.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim score As String, result As String
score = Range("A1").Value

If score = "1" Or score="book1" Then
   result = "book1"
ElseIf score = "2" Or score="book2" Then
   result = "book2"
Else
   result = "wrong"
End If

Range("A1").Value = result
End Sub

edited Feb 4, 2020 at 5:05

answered Feb 4, 2020 at 4:32

Naveen Kumar

1,9961 gold badge9 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

make insert statement in VBA Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related