Please Help with Code

[ReginaH]

Hello everyone

I'm kind of a noob working on Excel VBA and I need your help, please.
I'm doing a data entry form on VBA and I want it to block an entry of a client's Code Number (written in portuguese is "código") if its already registered, also when I add a new row of data I want that row to be protected, and then skip to the next row.
To do that I'm following this video https://www.youtube.com/watch?v=a9gRw_ZuSyE

My database goes starts on E6 to Q6 and has no end. Can you correct my code? the code I have is the following:

'''''''check the duplicate Código
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Base de Dados Profissionais")
Dim n As Long


If Application.WorksheetFunction.CountIf(sh.Range("E:E"), Me.TextBox1.Value) > 0 Then
MsgBox "Este Código já se encontra registado na Base de Dados de Profissionais.", vbCritical
Exit Sub
End If


n = sh.Range("E" & Application.Rows.Count).End(xlUp).Row
sh.Unprotect "00"




sh.Range("E" & n + 1).Value = Me.TextBox16.Value
sh.Range("G" & n + 1).Value = Me.TextBox1.Value
sh.Range("H" & n + 1).Value = Me.TextBox7.Value
sh.Range("I" & n + 1).Value = Me.TextBox8.Value
sh.Range("K" & n + 1).Value = Me.TextBox10.Value
sh.Range("M" & n + 1).Value = Me.TextBox12.Value
sh.Range("L" & n + 1).Value = Me.TextBox9.Value
sh.Range("N" & n + 1).Value = Me.TextBox11.Value
sh.Range("Q" & n + 1).Value = Me.TextBox15.Value
sh.Range("P" & n + 1).Value = Me.TextBox14.Value
sh.Range("O" & n + 1).Value = Me.TextBox13.Value




If Me.OptionButton1.Value = True Then sh.Range("F" & n + 1).Value = "Empresa"
If Me.OptionButton2.Value = True Then sh.Range("F" & n + 1).Value = "Particular"
sh.Range("J" & n + 1).Value = Me.ComboBox1.Value


sh.Protect "00"

End Sub

[SamT]

Dim CodigoExiste As Boolean
Dim Found As Range
Set Found = sh.Range("E:E").Find(Me.TextBox1.Value)
CodigoExiste = Not (Found = Nothing)
If  CogidoExiste Then 
   n = Found.Row
   MsgBox "blahBlah
Else 
   n = sh.Cells(Rows.Count, "E").End(xlUp).Row + 1
End If
'And only n, no n + 1 in the rest
'BlahBlah

[snb]

sh.cells(n + 1,6) = iif(OptionButton1,"Empresa","Particular")

[ReginaH]

Dim CodigoExiste As Boolean
Dim Found As Range
Set Found = sh.Range("E:E").Find(Me.TextBox1.Value)
CodigoExiste = Not (Found = Nothing)
If  CogidoExiste Then 
   n = Found.Row
   MsgBox "blahBlah
Else 
   n = sh.Cells(Rows.Count, "E").End(xlUp).Row + 1
End If
'And only n, no n + 1 in the rest
'BlahBlah

Gives a error message of Compilaton Error: Use of Invalid Object , how can I fix it?
I've correct the typo in the word "Código"

[p45cal]

try (untested):

'''''''check the duplicate Código
Dim n As Long
With ThisWorkbook.Sheets("Base de Dados Profissionais")
  If Application.CountIf(.Range("E:E"), TextBox1.Value) = 0 Then
    n = .Range("E" & .Rows.Count).End(xlUp).Row + 1
    .Unprotect "00"

    .Range("E" & n).Value = TextBox16.Value
    If OptionButton1.Value Then .Range("F" & n).Value = "Empresa"
    If OptionButton2.Value Then .Range("F" & n).Value = "Particular"
    .Range("G" & n).Resize(, 11).Value = Array(TextBox1.Value, TextBox7.Value, TextBox8.Value, ComboBox1.Value, TextBox10.Value, TextBox9.Value, TextBox12.Value, TextBox13.Value, TextBox11.Value, TextBox14.Value, TextBox15.Value)
    
    .Protect "00"
  Else
    MsgBox "Este Código já se encontra registado na Base de Dados de Profissionais.", vbCritical
  End If
End With
End Sub

Were you getting an error message?

[p45cal]

Gives a error message of Compilaton Error: Use of Invalid Object , how can I fix it?
I've correct the typo in the word "Código"

Try changing:

CodigoExiste = Not (Found = Nothing)
If CogidoExiste Then

to:

If Not Found is Nothing Then

[ReginaH]

try (untested):

    .Unprotect "00"

Were you getting an error message?

1º Error 424 from that code you gave me.
The user form is now working in the correct range of cells, but it's not protecting the database for repeated clients codes.

2º however now a new error appeared: in the userform when I click the commandbutton to "add new user" after typing the user info, now I have to click twice on VBA Play button to add it
This database (it's a mess I now) is just to practice code and learn a bit more because I'd like to adapt it and use it in my job as a personalized and safer database template. I still need to add a second command button to remove or edit specific data.
I'm sending the file if it helps.


Thanks a lot for the help guys

[p45cal]

You should be using the right textbox; instead of:

If Application.CountIf(.Range("E:E"), Me.TextBox1.Value) = 0 Then

If Application.CountIf(.Range("E:E"), Me.TextBox16.Value) = 0 Then

[ReginaH]

You should be using the right textbox; instead of:

If Application.CountIf(.Range("E:E"), Me.TextBox1.Value) = 0 Then

If Application.CountIf(.Range("E:E"), Me.TextBox16.Value) = 0 Then

It worked! Thank you!

Now I am facing other problem, following this video https://www.youtube.com/watch?v=FB_pm9wt6jI

Private Sub ComboBox2_Change()If Me.ComboBox2.Value <> "" Then
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Base de Dados Profissionais")
    Dim i As Integer
    
    i = Application.Match(VBA.CLng(Me.ComboBox2.Value), sh.Range("E:E"), 0)
    
    Me.TextBox1.Value = sh.Range("G" & i).Value
    Me.TextBox7.Value = sh.Range("H" & i).Value
    Me.TextBox8.Value = sh.Range("I" & i).Value
    Me.TextBox10.Value = sh.Range("K" & i).Value
    Me.TextBox9.Value = sh.Range("L" & i).Value
    Me.TextBox12.Value = sh.Range("M" & i).Value
    Me.TextBox11.Value = sh.Range("N" & i).Value
    Me.TextBox15.Value = sh.Range("Q" & i).Value
    Me.TextBox14.Value = sh.Range("P" & i).Value
    Me.TextBox13.Value = sh.Range("O" & i).Value
    
    If sh.Range("F" & i).Value = "Empresa" Then Me.OptionButton3.Value = True
    If sh.Range("F" & i).Value = "Particular" Then Me.OptionButton1.Value = True
    
    Me.ComboBox1.Value = sh.Range("J" & i).Value
    
End If
End Sub

Basically I duplicated the first userform and created a commandbutton to update/edit client's data. I replaced the TextBox used to insert clients code to a Combobox to search for the code that i want to update.
That line of code in red gives me the error "runtime error 13 - Incompatible types"

[p45cal]

I'll need to see the workbook please.

[ReginaH]

I'll need to see the workbook please.

Attached. It's a little messy, sorry for that. It's just to practice code.Base de Dados.xlsm

[p45cal]

It looks like Codigo can contain alphanumeric values, not just numeric ones.
You can try changing that little sub to:

Private Sub ComboBox2_Change()
If Me.ComboBox2.Value <> "" Then
  Dim sh As Worksheet
  Set sh = ThisWorkbook.Sheets("Base de Dados Profissionais")
  Dim i As Long
  Set rngCod = sh.Range("E:E").Find(ComboBox2.Value, LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
  If Not rngCod Is Nothing Then
    i = rngCod.Row
    Me.TextBox1.Value = sh.Range("G" & i).Value
    Me.TextBox7.Value = sh.Range("H" & i).Value
    Me.TextBox8.Value = sh.Range("I" & i).Value
    Me.TextBox10.Value = sh.Range("K" & i).Value
    Me.TextBox9.Value = sh.Range("L" & i).Value
    Me.TextBox12.Value = sh.Range("M" & i).Value
    Me.TextBox11.Value = sh.Range("N" & i).Value
    Me.TextBox15.Value = sh.Range("Q" & i).Value
    Me.TextBox14.Value = sh.Range("P" & i).Value
    Me.TextBox13.Value = sh.Range("O" & i).Value
    
    If sh.Range("F" & i).Value = "Empresa" Then Me.OptionButton3.Value = True
    If sh.Range("F" & i).Value = "Particular" Then Me.OptionButton1.Value = True
    
    Me.ComboBox1.Value = sh.Range("J" & i).Value
  Else
    MsgBox "Code does not exist"
  End If
End If
End Sub

Obviously you have more to do in the other subs.

[SamT]

Regina,

I looked at your book.

While I was able to save the code in another program, my machine is messed up and I could not save a copy of your book. I did correct many errors in the Form and in its code. Note that I first changed all the controls names to meaningful values. These Control name appear in the below code.

Option Explicit



Private Sub cbutAdicionarProfissional_Click()
        '''''''validation'''''''
'        Many of these "IsNumeric" checks are being applied to data that is Text, not numerical. Obviously, I did not correct these
        If VBA.IsNumeric(Me.txbNIPC.Value) = False Then
            MsgBox "Este campo só aceita valores numéricos.", vbCritical
            Exit Sub
        End If
        
        If VBA.IsNumeric(Me.tbxCodigo.Value) = False Then
            MsgBox "Este campo só aceita valores numéricos.", vbCritical
            Exit Sub
        End If
        
        If VBA.IsNumeric(Me.txbValorServico.Value) = False Then
            MsgBox "Este campo só aceita valores numéricos. Insira o Valor do Serviço.", vbCritical
            Exit Sub
        End If
        
        If Me.tbxNome.Value = "" Then
            MsgBox " Insira o nome do Profissional.", vbCritical
        End If
        
        If Me.txbMorada.Value = "" Then
            MsgBox " Insira a Morada.", vbCritical
        End If
        If Me.txbEspecialidade.Value = "" Then
            MsgBox " Insira a Especialidade.", vbCritical
        End If
        
        If Me.txbCedula.Value = "" Then
            MsgBox " Insira a Cédula Profissional.", vbCritical
        End If
        
        If Me.txbDisponibilidade.Value = "" Then
            MsgBox " Insira a Disponibilidade.", vbCritical
        End If
    
       If Me.txbObservacoes.Value = "" Then
            MsgBox " Insira Obsservações", vbCritical
        End If
        
             If Me.tbxE_Mail.Value = "" Then
            MsgBox " Insira o E-Mail.", vbCritical
        End If
        
        
        If Me.optEmpresa.Value = False And Me.optParticular.Value = False Then
        MsgBox "Selecione o Tipo de Profissional: Particular ou Empresa.", vbCritical
        End If
        
        If Me.cmbZona.Value = "" Then
        MsgBox "Selecione uma Zona.", vbCritical
        End If
        

'''''''check the duplicate Código
Dim n As Long
With ThisWorkbook.Sheets("Base de Dados Profissionais")
  If Application.CountIf(.Range("E:E"), Me.tbxCodigo.Value) = 0 Then
    n = .Range("E" & .Rows.Count).End(xlUp).Row + 1
    .Unprotect "1234"

    .Range("E" & n).Value = tbxCodigo.Value
    If optParticular.Value Then .Range("F" & n).Value = "Particular"
    If optEmpresa.Value Then .Range("F" & n).Value = "Empresa"
    .Range("G" & n).Resize(, 11).Value = Array(tbxNome.Value, txbNIPC.Value, txbMorada.Value, cmbZona.Value, txbEspecialidade.Value, txbCedula.Value, txbDisponibilidade.Value, txbValorServico.Value, tbxE_Mail.Value, txtTelefone.Value, txbObservacoes.Value)
    
    .Protect "1234"
  Else
    MsgBox "Este Código já se encontra registado na Base de Dados de Profissionais.", vbCritical
  End If
End With
End Sub


Private Sub cbutLimparProfissional_Click()
    Me.tbxNome.Value = ""
    Me.txbNIPC.Value = ""
    Me.txbMorada.Value = ""
    Me.txbEspecialidade.Value = ""
    Me.txbCedula.Value = ""
    Me.txbDisponibilidade.Value = ""
    Me.txbValorServico.Value = ""
    Me.txbObservacoes.Value = ""
    Me.txtTelefone.Value = ""
    Me.txbValorServico.Value = ""
    Me.tbxCodigo.Value = ""
    Me.cmbZona.Value = ""
    Me.frmTipo.Controls.optParticular.Value = False
    Me.frmTipo.Controls.optEmpresa.Value = False
End Sub


Private Sub UserForm_Activate()
With Me.cmbZona
    .Clear
    .AddItem ""
    .AddItem "Norte"
    .AddItem "Centro"
    .AddItem "Sul"
    .AddItem "Madeira"
    .AddItem "Açores"
End With
End Sub

[SamT]

I have been playing with your Form. First I deleted everything except the Form and the single DB sheet.

I use a little bit of advanced VBA on the Worksheet code. See tutorials on Excel VBA Class Modules and on Property Get Let Set. I treat Database sheets as class Modules, Everything to do with the sheet, I let the sheet's code do, and just pass data from the sheet to the Form and vice versa.

looking at the Profissionals sheet, you will see a couple of sections I used to generate code. In the Sheet's Code page you will see that I used Class Properties to "send" and "receive" data. YOu will notice that I reformatted the sheet to better suit a sheet used as a Database.

looking at the VBA Project Explorer window, you can see that I changed the DB sheet's CodeName (AKA, SheetObject Name) and used the CodeName in the UserForm code, this is because I am treating the Sheet as a Class Module, (AKA, an Object.)

Looking at the UserForm, you will notice that I changed all the Control Names to suit both the Form Labels and the Sheet's Headers. This makes the code much easier to check for accuracy and allows me to merely Cut&Paste instead of making many typos while typing.

I used the same form to both Edit and to Add new Records to the DataBase; Which, depended on the Codigo being present or not in the DB. The UserForm doesn't "Care", it is up to the Sheet's Code to handle that. See the usage of pWorkingRow in the sheet's code.

I have tested the Form a bit and it seems to be working fine.

[SamT]

I have been playing with your Form. First I deleted everything except the Form and the single DB sheet.

I use a little bit of advanced VBA on the Worksheet code. See tutorials on Excel VBA Class Modules. I treat Database sheets as class Modules, Everything to do with the sheet, I let the sheet's code do, and just pass data from the sheet to the Form and vice versa.

Looking at the Profissionals sheet, you will see a couple of sections I used to generate code. In the Sheet's Code page you will see that I used Class Properties to "send" and "receive" data. I did replace the Command buttons on the sheet with a "Toolbox" commandButton, since the ToolBox Controls are much easier to customize.

looking at the VBA Project Explorer window, you can see that I changed the DB sheet's CodeName (AKA, SheetObject Name) and used the CodeName in the UserForm code, this is because I am treating the Sheet as a Class Module..

Looking at the UserForm, you will notice that I changed all the Control Names to suit both the form Labels and the Sheet's Headers. This makes the code much easier to check for accuracy and allows me to merely Cut&Paste instead of making many typos while typing.

I used the same Form to both Edit and to Add new Records to the DataBase; Which depended on the Codigo being present or not in the DB. The UserForm doesn't "Care", It is up to the Sheet's Code to handle that. See pWorkingRow in the sheet's code.


Note that all VBA is in USoA English, but I did not edit any words on the Sheet.

I have tested the Form a bit and it seems to be working fine.

[ReginaH]

I have been playing with your Form. First I deleted everything except the Form and the single DB sheet.

I use a little bit of advanced VBA on the Worksheet code. See tutorials on Excel VBA Class Modules. I treat Database sheets as class Modules, Everything to do with the sheet, I let the sheet's code do, and just pass data from the sheet to the Form and vice versa.

Looking at the Profissionals sheet, you will see a couple of sections I used to generate code. In the Sheet's Code page you will see that I used Class Properties to "send" and "receive" data. I did replace the Command buttons on the sheet with a "Toolbox" commandButton, since the ToolBox Controls are much easier to customize.

looking at the VBA Project Explorer window, you can see that I changed the DB sheet's CodeName (AKA, SheetObject Name) and used the CodeName in the UserForm code, this is because I am treating the Sheet as a Class Module..

Looking at the UserForm, you will notice that I changed all the Control Names to suit both the form Labels and the Sheet's Headers. This makes the code much easier to check for accuracy and allows me to merely Cut&Paste instead of making many typos while typing.

I used the same Form to both Edit and to Add new Records to the DataBase; Which depended on the Codigo being present or not in the DB. The UserForm doesn't "Care", It is up to the Sheet's Code to handle that. See pWorkingRow in the sheet's code.


Note that all VBA is in USoA English, but I did not edit any words on the Sheet.

I have tested the Form a bit and it seems to be working fine.

Thank you so much for your help and patience!!! )) That's what I needed!