Userform Activation - Only once within cell upon Enter key (VBA)

[elitef]

Hello everyone

Really hoping someone can help me as I have been stuck all day trying to figure this out...
I have a spreadsheet which I am working on. I have VBA code which enables a userform depending on the value of a cell

Private Sub Worksheet_Change(byVal Target As Range)
If [A1].Value = "OTHER" Then
UserFormBox.Show
End If
End Sub

A1 is a drop down via Data Validation with 1,2,3,OTHER
As soon as Other is selected the userform box opens.

The userform that opens is a box with a single text field in it and an OK button. Upon clicking the OK button, it puts the value of the text field in B1

THE ISSUE THAT I AM HAVING IS:

Every time i go into any other cell and press enter in it, it displays the userform box again and again
I need the box to only be displayed when "OTHER" is selected in A1, but nowhere else.

I really hope someone can help me because I am totally clueless at this point...

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  5/13/2021  6:57:18 PM  EDT
If Target.Address = Range("A1").Address And Target.Value = "OTHER" Then UserFormBox.Show
End Sub

 

[Marc L]

According to Logic :​

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then If Target.Text = "OTHER" Then UserFormBox.Show
End Sub

 

[elitef]

Forgot to mentioned, because I forgot myself really since the behavior is the same, but at A1 I have a combobox (ComboBoxList) which uses the same range as the data validation.
So the above works, BUT when entering Other in the combobox and pressing Enter, it does not pop up the Userform box, instead, I have to go back to the A1 cell after for the box to populate

Any ideas?

 

[My Aswer Is This]

Show us the code you have in the Combobox.
If your using a combobox you do not need the the combobox to enter a value in Range("A1")

The script would be sort of like if combox1.value= Userform1 then UserForm1.show

 

[elitef]

Show us the code you have in the Combobox.
If your using a combobox you do not need the the combobox to enter a value in Range("A1")

The script would be sort of like if combox1.value= Userform1 then UserForm1.show

I've already tried
If ComboBoxList.Value = "Other" Than UserFormBox.show

But, the problem with that is that since "Other" remains in A1, any other cell that I go to and type something in and then press Enter or any other way of getting out of focus of said cell, the UserFormBox shows up again because technically Other is still listed in A1....

Thats the dilemma that I have been having.

 

[My Aswer Is This]

I've already tried
If ComboBoxList.Value = "Other" Than UserFormBox.show

But, the problem with that is that since "Other" remains in A1, any other cell that I go to and type something in and then press Enter or any other way of getting out of focus of said cell, the UserFormBox shows up again because technically Other is still listed in A1....

Thats the dilemma that I have been having.

That is because your original code you showed was not written correctly.
Two of us provided code that would work. Did you try the code I provided?

 

[elitef]

That is because your original code you showed was not written correctly.
Two of us provided code that would work. Did you try the code I provided?

So what you provided worked, but ONLY if "OTHER" is being typed in A1 directly, instead of in the ComboBoxList combobox
When I change the If statement to reflect
If ComboBoxList.Value = "OTHER" Then UserFormBox.Show
The box opens, but upon pressing Enter, I get a runtime error : Run-Time Error 400 Form already displayed; can't show modally

 

[My Aswer Is This]

I'm not sure what your wanting to do.
First I thought you wanted to enter "OTHER" In range("A1") to have the Userform open.
But then you started talking about choosing a value in a combobox.
So I'm confused about what your wanting.

 

[elitef]

I'm not sure what your wanting to do.
First I thought you wanted to enter "OTHER" In range("A1") to have the Userform open.
But then you started talking about choosing a value in a combobox.
So I'm confused about what your wanting.

Apologies
I need for the userform box to appear when Other is entered in a combobox. That combobox is kind of hidden since i made it blend with the cell so forgot it was a combo box as opposed to just a cell, so that was my bad.