combobox searchable

[ZTK]

Again requesting your help .......

I found a macro for drop-down lists, it works perfect for me as it creates the combobox in any "simple" drop-down list so there is no need to copy it manually.


I would like the combobox to have the search function, to filter the possible texts to use.

I show you the macro I use:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Update by Extendoffice: 2020/05/19

Dim xCombox As OLEObject

Dim xStr As String

Dim xWs As Worksheet

Dim xArr



Set xWs = Application.ActiveSheet

On Error Resume Next

If Target.Validation.Type = 3 Then

Target.Validation.InCellDropdown = False

Cancel = True

xStr = Target.Validation.Formula1

xStr = Right(xStr, Len(xStr) - 1)

If xStr = "" Then Exit Sub

Set xCombox = xWs.OLEObjects("TempCombo")

With xCombox

.ListFillRange = ""

.LinkedCell = ""

.Visible = False

End With

With xCombox

.Visible = False

.Left = Target.Left

.Top = Target.Top

.Width = Target.Width + 3

.Height = Target.Height + 3

.ListFillRange = xStr

If .ListFillRange = "" Then

xArr = Split(xStr, ",")

Me.TempCombo.List = xArr

End If

.LinkedCell = Target.Address

End With

xCombox.Activate

Me.TempCombo.DropDown

End If

End Sub

Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

Select Case KeyCode

Case 9

Application.ActiveCell.Offset(0, 1).Activate

Case 13

Application.ActiveCell.Offset(1, 0).Activate

End Select

End Sub




thanks in advance

 

[ZTK]

You can share your book on google drive.
And explain step by step what you do so that I can reproduce the problem you have.

In the same file that you shared, without modifying anything it gives me the detail, when "enabling content"

When entering a value in any cell that does not have a validated list, it does not allow me to undo what was written

Without macro enabled it does not cause me a problem

 

[DanteAmor]

In the file that I sent you, if I write in any cell, which does not have a validation list, i press enter and then press control Z, it works.

As I mentioned, it works. I can edit cells, hit enter. Press control Z and it works.
But if you modify a cell "x1", then you modify a cell with a validation list, and you want with control Z to undo what you put in cell "x1" is no longer possible. Running a macro clears the memory.
If you want to keep the changes of everything you do, you would have to create a code that saves all the changes, and that is very complicated.

 

[ZTK]

Yes, of course, what is done with the macro cannot be undone

For later that I get home I will upload the file without the other 2 macros that it has to see if you support me with the error

I also have the file in its "old version" it had the same problem but I managed to adapt the macro to fix it and it worked fine

Thanks again and sorry for the inconvenience

 

[ZTK]

You can share your book on google drive.
And explain step by step what you do so that I can reproduce the problem you have.

DanteAmor

I uploaded the file again, no more macros than the one you helped me with

The "undo" problem is given as I said by adding values anywhere in the document

For example modify cells B13, B14 and B15 and it won't let me undo

As a reference I use it with office 2013, I don't know if that has something to do with it

Thanks again for the help

BITX 2.xlsm

BITA BITACORA INFO Sistema 3, XXCV, DCFG, SSED, REGISTRO ID,SUC,HORA,A C T I V I D A D , , , , , , NOVED ID,SUCURSAL,HORA,A C T I V I D A D , , , NOVED ID,SUCURSAL,HORA,A C T I V I D A D , , , , , , , , FALLAS ID,AAA,HORA,FALLA , , , , , , , , F A L L A S E N C ...

docs.google.com

 

[DanteAmor]

In the file that I sent you, if I write in any cell, which does not have a validation list, i press enter and then press control Z, it works.

As I mentioned, it works. I can edit cells (no have a validation list), hit enter. Press control Z and it works.

 

[ZTK]

Well no way

I will try again to see if I find what detail it has

Thank you so much

 

[ZTK]

Como mencioné, funciona. Puedo editar celdas (no tengo una lista de validación), presione enter. Presione el control Z y funciona.

Mil disculpas, acabo de abrir el enlace que compartí y veo que se agregó como hojas de google (desactivando la macro)

Aquí subo el archivo para descargar

DanteAmor, ¿podrías apoyarme revisándolo y confirmando si en este documento ves el error que menciono?


Muchas gracias

BITX2.xlsm

Shared with Dropbox

www.dropbox.com

 

[ZTK]

As I mentioned, it works. I can edit cells (no have a validation list), hit enter. Press control Z and it works.

A thousand apologies, I just opened the link that I shared and I see that it was added as google sheets (deactivating the macro)

Here I upload the file for download

DanteAmor, could you support me by reviewing it and confirming if in this document you see the error that I mention?


Thanks a lot

BITX2.xlsm

Shared with Dropbox

www.dropbox.com

 

[DanteAmor]

it was added as google sheets

I don't have google sheets for testing.
Tested with 2007 and it works, as I already mentioned.

 

[ZTK]

Okay

Is it then that between 2007 (which you use) and 2013 (which I have) some incompatibility is generated or something like that?