AutoComplete doesn't recognize Cell A1, it starts getting information from the second

[jrsilverio]

AutoComplete doesn't recognize Cell A1, it starts getting information from the second.
Also, can anyone clean this code?
VBA Excel
:crying:



Option Explicit
Private AutoCompleteRange As Range
Private IgnoreChange As Boolean

Private Sub TextBox4_Change()
Dim ACText As String
Dim UserText As String
If IgnoreChange Then
IgnoreChange = False
Exit Sub
End If
UserText = Replace(TextBox4.Text, TextBox4.SelText, "")
ACText = AutoCompleteRange.AutoComplete(UserText)
IgnoreChange = True
If ACText = "" Then
TextBox4.Text = UserText
Else
TextBox4.Text = ACText
TextBox4.SelStart = Len(UserText)
TextBox4.SelLength = Len(ACText) - Len(UserText)
End If
IgnoreChange = False
End Sub

Private Sub TextBox4_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 8 Then
IgnoreChange = True
End If
End Sub

Private Sub UserForm_Initialize()
Set AutoCompleteRange = Planilha2.Range("A1")
End Sub

[snb]

I overlooked the sample Excel File.

[jrsilverio]

I overlooked the sample Excel File.

30221

[p45cal]

You're right, it seems to ignore the actual cell referred to in the line:
Set AutoCompleteRange = Planilha2.Range("A1")
So move the list down one cell and give it a header.
Also note in the help for AutoComplete: 'If there's no AutoComplete match or if more than one entry in the list matches the string to complete, this method returns an empty string.'
But, wouldn't you be better using a combobox? See the attached where I've added one just above the button Cadastrar which seems to do what you want. Just start typing in the box, it will autocomplete.

[snb]

Instead of 'using' disabled textboxes you'd better use Labels.

[jrsilverio]

You're right, it seems to ignore the actual cell referred to in the line:
Set AutoCompleteRange = Planilha2.Range("A1")
So move the list down one cell and give it a header.
Also note in the help for AutoComplete: 'If there's no AutoComplete match or if more than one entry in the list matches the string to complete, this method returns an empty string.'
But, wouldn't you be better using a combobox? See the attached where I've added one just above the button Cadastrar which seems to do what you want. Just start typing in the box, it will autocomplete.

About the ComboBox, yes, I think it could be better, but as I am the apprentice's apprentice, there are certain things that I am not aware of, but the principle would be the following below, but it gives an error.



Dim x As Long, ncell As Long, Z As Integer
Dim Rng As Range, c As Range
Dim ws As Worksheet
Dim strSheet(2) As String, ctrl(2) As String
strSheet(0) = "Planilha2"
strSheet(1) = "Planilha3"
strSheet(2) = "Planilha4"
strSheet(3) = "Planilha5"
strSheet(4) = "Planilha6"
ctrl(0) = "ComboBox1"
ctrl(1) = "ComboBox2"
ctrl(2) = "ComboBox3"
ctrl(3) = "ListBox1"
ctrl(4) = "ListBox2"
For Z = 0 To 4
Set ws = ThisWorkbook.Worksheets(strSheet(Z))
With ws
ncell = .Cells(.Rows.Count, "A").End(xlUp).Row
Me.Controls(ctrl(Z)).Clear
For y = 1 To ncell
Set c = .Cells(y, 1)
Set Rng = .Range(.Cells(2, 1), .Cells(y, 1))
x = Application.WorksheetFunction.CountIf(Rng, c)
If x = 1 Then Me.Controls(ctrl(Z)).AddItem c
Next y
End With
Exit Sub
Next
End Sub


https://www.tomasvasquez.com.br/forum/download/file.php?id=6519

[jrsilverio]

Instead of 'using' disabled textboxes you'd better use Labels.

I don't understand, do you have an example?

[p45cal]

but the principle would be the following below, but it gives an error.
OK, quite a few things wrong.
1. What version of Excel are you using?
2. Do you have the UNIQUE function available to you on a worksheet?
3. Could you attach a fresh up-to-date workbook with the changes you have made regarding changing controls on the userform to comboboxes etc. and incorporating the code you quoted in your message?

I'll try to give you a workable solution.

[jrsilverio]

Microsoft Professional Plus 2021

[p45cal]

With only minimal changes to your current code:
Private Sub UserForm_Initialize()
TextBox3 = Date
TextBox3.Enabled = False
ListBox1.Enabled = False
TextBox6.Enabled = False
'--------'
Dim pw As String
Dim i As Integer
Randomize
For i = 1 To 6
If Int((2 * Rnd) + 1) = 1 Then
pw = pw & Chr(Int(26 * Rnd + 65))
Else
pw = pw & Int(10 * Rnd)
End If
Next i
TextBox6.Text = pw
'--------'
Dim x As Long, ncell As Long, Z As Integer
Dim Rng As Range, c As Range
Dim ws As Worksheet
'Define matriz com planilhas e controles elegiveis
Dim strSheet(0 To 4) As String, ctrl(0 To 4) As String
strSheet(0) = "Planilha2"
strSheet(1) = "Planilha3"
strSheet(2) = "Planilha4"
strSheet(3) = "Planilha5"
strSheet(4) = "Planilha6"
ctrl(0) = "ComboBox1" 'Planilha2
ctrl(1) = "ComboBox2" 'Planilha3
ctrl(2) = "ComboBox3" 'Planilha4
ctrl(3) = "ListBox1" 'Planilha5
ctrl(4) = "ListBox2" 'Planilha6
For Z = 0 To 4
Set ws = ThisWorkbook.Worksheets(strSheet(Z))
With ws
ncell = .Cells(.Rows.Count, "A").End(xlUp).Row
Me.Controls(ctrl(Z)).Clear

For y = 1 To ncell
Set c = .Cells(y, 1)
Set Rng = .Range(.Cells(2, 1), .Cells(y, 1))
x = Application.CountIf(Rng, c)
If x = 1 Then Me.Controls(ctrl(Z)).AddItem c
Next y
End With
Next
End Sub
and it could be shorter:
Private Sub UserForm_Initialize()
Dim mySheets, myControls, sht, uniqueList
TextBox3 = Date
TextBox3.Enabled = False
ListBox1.Enabled = False
TextBox6.Enabled = False
'--------'
Dim pw As String
Dim i As Integer
Randomize
For i = 1 To 6
If Int((2 * Rnd) + 1) = 1 Then
pw = pw & Chr(Int(26 * Rnd + 65))
Else
pw = pw & Int(10 * Rnd)
End If
Next i
TextBox6.Text = pw
'--------'
Dim ncell As Long
'Define matriz com planilhas e controles elegiveis
Set mySheets = Sheets(Array("Planilha2", "Planilha3", "Planilha4", "Planilha5", "Planilha6"))
myControls = Array(ComboBox1, ComboBox2, ComboBox3, ListBox1, ListBox2)
i = LBound(myControls)
For Each sht In mySheets
With sht
ncell = .Cells(.Rows.Count, "A").End(xlUp).Row
uniqueList = Application.Unique(.Range("A1:A" & ncell))
myControls(i).List = uniqueList
'myControls(i).List = Application.Unique(.Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)) 'the above 3 lines condensed to 1.
i = i + 1
End With
Next
End Sub

[snb]

Avoid redundant variables.


For Each it In Sheets
myControls(it.index-1).List = Application.Unique(it.columns(1).specialcells(2))
Next

[jrsilverio]

There was a problem that I forgot to mention, when selecting a "buyer" option in ComboBox8 (already developed code that pulls information from column A), its class in ComboBox9 needs to be filled with information from Column B of Sheet3.


sorry for the inconvenience

30238

30237

[p45cal]

See attached and comments in the code.

[jrsilverio]

wow, you are a god, thank you very much! The forum lacked a star field for you to earn points.

[suculeiz1]

[/CODE]

The opposite way of doing this, instead of choosing several sheets..., would choose 1, however, would delimit which column of the chosen one would be used in a textbox1 or textbox2 for example?




Dim mySheets, myControls, sht, uniqueList
Dim ncell As Long
Set mySheets = Sheets(Array("Plan1"))
myControls = Array(TextBox1, TextBox2, TextBox3, TextBox4, TextBox5)
i = LBound(myControls)
For Each sht In mySheets
With sht
myControls(i).List = Application.Unique(.Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)) 'the above 3 lines condensed to 1.
i = i + 1
End With
Next
End Sub