VBA doesn't allow copy/paste

[Salad Fingers]

I have a workbook with several sheets. Only in one of them I have a VBA code::
Private Sub Worksheet_SelectionChange (ByVal Target As Range)
Application.Calculate
End Sub

The VBA code doesn't allow me to copy/paste from one cell to another in this particular sheet.
Could you help me with a solution to have the ability to copy/paste without removing the VBA?
If you need more information, I would gladly provide it.
Thanks

 

[Joe4]

Welcome to the Board!

That code will essentially abandon/kill any copy/paste in progress as you move from one cell to another.
What is the purpose of this code?
I am thinking that there is a probably a better way to do with whatever it is you are trying to accomplish with that bit of code.

 

[Salad Fingers]

I am using it in order to have multiple searchable drop down lists in the same sheet. Otherwise they won't work. There might be a better way, but I am a beginner so I am not too sure how to go about it.

I watched this video and replicated the method shown in it:

 

[Joe4]

If you need it to recalculate when some value is selected, you can limit it to run JUST on the update of specific cells (using Target).
That way it doesn't disable that for all cells.

 

[Salad Fingers]

How to use the Target function?

 

[Joe4]

"Target" isn't a function, it is the parameter used in this VBA code.
In event procedure VBA code like this, "Target" is the range that triggered this code to run (it is right in the first line, i.e.):

Private Sub Worksheet_SelectionChange (ByVal Target As Range)

So, what you can do is check to see in the "Target" range (the range that triggered this code to run) is the range you want the update to run on.
Quite frankly, I think it is better to use the "Worksheet_Change" event procedure, which only runs when data is changed, as opposed to "Worksheet_SelectionChange", which runs whenever a cell is selected.

For example, if your drop-down boxes were in cells A1, A10, and A25, you could write your code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Or Target.Address = "$A$10" Or Target.Address = "$A$25" Then
        Application.Calculate
    End If
End Sub

I think that should do what you want. You just need to identify all the cell addresses with your interactive drop-down boxes.

 

[Salad Fingers]

I tried this way, however once I copy and paste a cell my dropdown menus stop working. Also i need to be able to copy and paste other cells without the dropdown list

 

[Joe4]

Which cells contain your drop-downs?
Which cell are you trying to copy specifically that is causing you issues?
What is the purpose of copy/pasting that cell?

 

[Salad Fingers]

My table is A:S with 35 rows. My searchable drop down lists are contained in column F (every row of the column contains a searchable drop down list)
I am trying to have a table where every cell could be copy / pasted (doesn't matter if it is a cell containing the searchable list or a "normal" cell with text in it.
The purpose of copy/pasting around is to save time filling the table.
My problem is that when I copy a cell, my searchable drop downs stop working (when I click the drop down list doesn't show)

 

[Akuini]

@Salad Fingers
See if this helps:
I created an add-in called “Search deList”, and I shared it as a freeware, and also the code is not protected so you can check it & amend it as needed.
Its function is to speed up searching in data validation list. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox. You can type multiple keywords (separated by a space) in the combobox and the list will be narrowed down as you type.
You can use up-down arrow to select an item, then hit ENTER, the selected item will be inserted into the cell, and the Userform will be closed.
And also you don't need vba.
I posted the add-in in this thread: LINK

OR

If you don't want to install the add-in, there's another option, which is using vba. See in this thread: LINK