Jump to content
betofilmes

Não grava nas planilhas

Recommended Posts

betofilmes

Ola Pessoal podem ajudar?

O que necessito

estou fazendo uma Planilha de Discurso

Tenho 2 SAlas - SALA A e SALA B

Em cada SAla haverá 3 PAlestras

cRIEI 3 Planilhas - para facilitar o controle - CadPartesSalaA, CadPartesSalaB e CadDestaquesBBB.

mas estou com problemas para gravar, alterar,

Uma outra coisa, esta configurado para numeração automatica, mas neste caso irei usar Data, não consegui fazer a configuração.

Espero por dicas, e assim puderem fornecer.

Option Explicit
'define constantes para controlar as colunas de dados
Const colData As Integer = 1
Const colDestaqueBBB As Integer = 2
Const colFonteMatDestaque As Integer = 3
Const colDisc1SalaA As Integer = 4
Const colEst1SalaA As Integer = 5
Const colFonteMat1SalaA As Integer = 6
Const colDisc2SalaA As Integer = 7
Const colEst2SalaA As Integer = 8
Const colFonteMat2SalaA As Integer = 9
Const colTema2SalaA As Integer = 10
Const colCena2SalaA As Integer = 11
Const colAjudante2SalaA As Integer = 12
Const colDisc3SalaA As Integer = 13
Const colEst3SalaA As Integer = 14
Const colFonteMat3SalaA As Integer = 15
Const colTema3SalaA As Integer = 16
Const colCena3SalaA As Integer = 17
Const colAjudante3SalaA As Integer = 18
Const colDisc1SalaB As Integer = 19
Const colEst1SalaB As Integer = 20
Const colFonteMat1SalaB As Integer = 21
Const colDisc2SalaB As Integer = 22
Const colEst2SalaB As Integer = 23
Const colFonteMat2SalaB As Integer = 24
Const colTema2SalaB As Integer = 25
Const colCena2SalaB As Integer = 26
Const colAjudante2SalaB As Integer = 27
Const colDisc3SalaB As Integer = 28
Const colEst3SalaB As Integer = 29
Const colFonteMat3SalaB As Integer = 30
Const colTema3SalaB As Integer = 31
Const colCena3SalaB As Integer = 32
Const colAjudante3SalaB As Integer = 33
Const indiceMinimo As Byte = 2

'define variavies para controlar a
Private alterar As Boolean
Private novo As Boolean
Private excluir As Boolean

'define as constantes para as cores do textbox
Const corDesabilitaTextBox As Long = -2147483633
Const corHabilitaTextBox As Long = -2147483643

'define a planilha usada e o indice do registro

Private wsCadPartesSalaA As Worksheet
Private wsCadPartesSalaB As Worksheet


Private indiceData As Long


Private Sub Cbo_Cena_Change()

End Sub


Private Sub Btn_alterar_Click()
alterar = True
   If txt_Data.Text <> vbNullString And txt_Data.Text <> "" Then
        Call HabilitaControles
        Call DesabilitaBotoesAlteracao
        'dá o foco ao primeiro controle de dados
        txt_Data.SetFocus
    Else
        lblMensagem.Caption = "Não há registo a ser alterado"
    End If
End Sub


Private Sub Btn_anterior_Click()

End Sub

Private Sub Btn_cancelar_Click()
Btn_ok.Enabled = False
    Btn_cancelar.Enabled = False
    Call DesabilitaControles
    Call carregaDados
    Call HabilitaBotoesAlteracao
End Sub

Private Sub Btn_excluir_Click()
excluir = True
    If txt_Data.Text <> vbNullString And txt_Data.Text <> "" Then
        Call DesabilitaBotoesAlteracao
        lblMensagem.Caption = "Você confirma a eliminação deste registo. (Para excluir clique no botão Guardar.) "
    Else
        lblMensagem.Caption = "Não existe registo a ser eliminado"
    End If
End Sub

Private Sub Btn_Fechar_Click()

If MsgBox("Você realmente deseja SAIR?", vbYesNo + vbQuestion, "Sair") = vbYes Then
         
End   'sair
Else
         'cancela
End If
End Sub

Private Sub Btn_novo_Click()
novo = True
    excluir = False
    alterar = False
    Call LimpaControles
    Call HabilitaControles
    Call DesabilitaBotoesAlteracao
    
    txt_Data.SetFocus
End Sub

Private Sub Btn_ok_Click()
'valida campos do formulário
    If ValidaCamposFormulario = False Then
        Exit Sub
    End If
    
    Dim proximoId As Long

    'Alterar registros
    If alterar = True Then
       
        lblMensagem.Caption = "Registro alterado com sucesso."
        alterar = False
    End If
    
    'Novo registro
    If novo = True Then
        Dim proximoIndice As Long
        Call SalvaRegistro(proximoId, proximoIndice)
        txt_Data = proximoId
        lblMensagem.Caption = "Novo registo Guardado com sucesso."
        novo = False
    End If
    
    'Excluir um registro
    If excluir = True Then
        Dim resultado As VbMsgBoxResult
        resultado = MsgBox("Deseja eliminar o registo nº " & txt_Data.Text & " ?", vbYesNo, "Confirmação")

        If resultado = vbYes Then
            wsCadPartesSalaA.Range(wsCadPartesSalaA.Cells(indiceData, colData), wsCadPartesSalaA.Cells(indiceData, colData)).EntireRow.Delete
            Call carregaDados
            lblMensagem.Caption = "O Registo escolhido foi eliminado com sucesso."
        End If
        excluir = False
    End If

    Call HabilitaBotoesAlteracao
    Call DesabilitaControles

End Sub

Private Sub Btn_primeiro_Click()


End Sub


Private Sub Btn_proximo_Click()

End Sub

Private Sub Btn_ultimo_Click()

End Sub

Private Sub Cbo_Discurso1_Change()

End Sub

Private Sub Cbo_Discurso3_Change()

End Sub

Private Sub CheckBox1_Click()

End Sub

Private Sub CheckBox2_Click()

End Sub

Private Sub chbox2a_Click()

Dim I As Long
Me.ComboBox11.Clear
For I = 2 To Plan8.Range("A65536").End(xlUp).Row
    Me.ComboBox11.AddItem Plan8.Range("C" & I).Value
Next I

End Sub

Private Sub chbox2aa_Click()

Dim I As Long
Me.ComboBox11.Clear
For I = 2 To Plan7.Range("A65536").End(xlUp).Row
    Me.ComboBox11.AddItem Plan7.Range("C" & I).Value
Next I

Me.ComboBox7.Clear
For I = 2 To Plan7.Range("A65536").End(xlUp).Row
    Me.ComboBox7.AddItem Plan7.Range("C" & I).Value
Next I

End Sub

Private Sub chbox2b_Click()
Dim I As Long
Me.ComboBox25.Clear
For I = 2 To Plan8.Range("A65536").End(xlUp).Row
    Me.ComboBox25.AddItem Plan8.Range("C" & I).Value
Next I

End Sub

Private Sub chbox2bb_Click()
Dim I As Long
Me.ComboBox25.Clear
For I = 2 To Plan7.Range("A65536").End(xlUp).Row
    Me.ComboBox25.AddItem Plan7.Range("C" & I).Value
Next I


Me.ComboBox22.Clear
For I = 2 To Plan7.Range("A65536").End(xlUp).Row
    Me.ComboBox22.AddItem Plan7.Range("C" & I).Value
Next I


End Sub

Private Sub chbox3a_Click()

Dim I As Long
Me.Cbo_Discurso3.Clear
For I = 2 To Plan8.Range("A65536").End(xlUp).Row
    Me.Cbo_Discurso3.AddItem Plan8.Range("C" & I).Value
Next I

End Sub

Private Sub chbox3aa_Click()
Dim I As Long
Me.Cbo_Discurso3.Clear
For I = 2 To Plan7.Range("A65536").End(xlUp).Row
    Me.Cbo_Discurso3.AddItem Plan7.Range("C" & I).Value
Next I

Me.ComboBox20.Clear
For I = 2 To Plan7.Range("A65536").End(xlUp).Row
    Me.ComboBox20.AddItem Plan7.Range("C" & I).Value
Next I


End Sub

Private Sub chbox3b_Click()
Dim I As Long
Me.ComboBox30.Clear
For I = 2 To Plan8.Range("A65536").End(xlUp).Row
    Me.ComboBox30.AddItem Plan8.Range("C" & I).Value
Next I
End Sub

Private Sub chbox3bb_Click()
Dim I As Long
Me.ComboBox30.Clear
For I = 2 To Plan7.Range("A65536").End(xlUp).Row
    Me.ComboBox30.AddItem Plan7.Range("C" & I).Value
Next I


Me.ComboBox35.Clear
For I = 2 To Plan7.Range("A65536").End(xlUp).Row
    Me.ComboBox35.AddItem Plan7.Range("C" & I).Value
Next I


End Sub



Private Sub ChBoxSalaA_Click()

End Sub

Private Sub coCena2_Change()

End Sub

Private Sub coCena2b_Change()

End Sub

Private Sub coCena3b_Change()

End Sub

Private Sub coDestaque_Change()


End Sub

Private Sub coEstudo3a_Change()

End Sub

Private Sub coFonteDestaque_Change()

End Sub

Private Sub coFonteMateria2a_Change()

End Sub

Private Sub ComboBox1_Change()

End Sub

Private Sub coFonteMateria2b_Change()

End Sub

Private Sub coFonteMateria3a_Change()

End Sub

Private Sub coFonteMateriab_Change()

End Sub

Private Sub ComboBox11_Click()


End Sub

Private Sub coFonteMateria_Change()

End Sub


Private Sub ComboBox15_Change()

End Sub

Private Sub ComboBox18_Change()

End Sub

Private Sub ComboBox14_Change()

End Sub

Private Sub ComboBox20_Change()

End Sub

Private Sub ComboBox24_Change()

End Sub

Private Sub ComboBox21_Change()

End Sub

Private Sub ComboBox22_Change()

End Sub

Private Sub ComboBox7_Change()

End Sub

Private Sub coTema2a_Change()

End Sub

Private Sub coTema3a_Change()

End Sub

Private Sub ScrollBar1_Change()
With Application
If .ScrollBar1 Then
ScrollBar1 = False

End With

End Sub


Private Sub lbData_Click()

End Sub

Private Sub txt_DATA_Change()
If Len(txt_Data) = 2 Then
txt_Data = txt_Data + "/"
txt_Data.SelStart = 4
End If
If Len(txt_Data) = 5 Then
txt_Data = txt_Data + "/"
txt_Data.SelStart = 7
End If

End Sub
Private Sub txt_Data_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If IsDate(Me.txt_Data.Text) = False And Me.txt_Data.Text <> Empty Then
        MsgBox ("A Data Digitada não é Válida")
        Me.txt_Data.SetFocus
    End If
    
End Sub

Private Sub UserForm_Initialize()
      
If Me.ChBoxSalaA.Value = "SalaA" Then
Sheets("CadPartesSalaA").Select

Dim SalaA As Range
Set SalaA = ActiveWorkbook.Sheets("CadPartesSalaA").Range("SalaA")

Do
If IsEmpty(SalaA) = False Then

Set SalaA = SalaA.Offset(1, 0)
End If
Loop Until IsEmpty(SalaA) = True

ActiveCell.Value = txt_Data
ActiveCell.Offset(0, 1).Value = Me.Cbo_Discurso1.Text
ActiveCell.Offset(0, 2).Value = Me.ComboBox10.Text
ActiveCell.Offset(0, 3).Value = Me.coFonteMateria.Text
ActiveCell.Offset(0, 4).Value = Me.ComboBox11.Text 'discurso 2
ActiveCell.Offset(0, 5).Value = Me.coEstudo2a.Text
ActiveCell.Offset(0, 6).Value = Me.coFonteMateria2a.Text
ActiveCell.Offset(0, 7).Value = Me.coTema2a.Text
ActiveCell.Offset(0, 8).Value = Me.coCena2.Text
ActiveCell.Offset(0, 9).Value = Me.ComboBox7.Text
ActiveCell.Offset(0, 10).Value = Me.Cbo_Discurso3.Text
ActiveCell.Offset(0, 11).Value = Me.coEstudo3a.Text
ActiveCell.Offset(0, 12).Value = Me.coFonteMateria3a.Text
ActiveCell.Offset(0, 13).Value = Me.coTema3a.Text
ActiveCell.Offset(0, 14).Value = Me.coCena3.Text
ActiveCell.Offset(0, 15).Value = Me.ComboBox20.Text

MsgBox "Dados Cadastrados com Sucesso!!"
Else



If Me.ChBoxSalaB = "SalaB" Then
Sheets("CadPartesSalaB").Select
Dim salab As Range
Set salab = ActiveWorkbook.Sheets("CadPartesSalaB").Range("SalaB")
Do
If IsEmpty(salab) = False Then
Set salab = salab.Offset(1, 0)
End If
Loop Until IsEmpty(salab) = True

ActiveCell.Value = txt_Data
   ActiveCell.Offset(0, 1).Value = Me.ComboBox21.Text
    ActiveCell.Offset(0, 2).Value = Me.ComboBox23.Text
   ActiveCell.Offset(0, 3).Value = Me.coFonteMateriab.Text
    ActiveCell.Offset(0, 4).Value = Me.ComboBox25.Text
    ActiveCell.Offset(0, 5).Value = Me.coEstudo2b.Text
    ActiveCell.Offset(0, 6).Value = Me.coFonteMateria2b.Text
    ActiveCell.Offset(0, 7).Value = Me.coTema2b.Text
    ActiveCell.Offset(0, 8).Value = Me.coCena2b.Text
    ActiveCell.Offset(0, 9).Value = Me.ComboBox22.Text
    ActiveCell.Offset(0, 10).Value = Me.ComboBox30.Text
    ActiveCell.Offset(0, 11).Value = Me.coEstudo3b.Text
    ActiveCell.Offset(0, 12).Value = Me.coFonteMateria3b.Text
    ActiveCell.Offset(0, 13).Value = Me.coTema3b.Text
    ActiveCell.Offset(0, 14).Value = Me.coCena3b.Text
    ActiveCell.Offset(0, 15).Value = Me.ComboBox35.Text
    
MsgBox "Dados Cadastrados com Sucesso!!"
    
    
    End If
    End If
    
    
  Dim I As Long
'FONTE MATERIA A
    Me.coFonteMateria.Clear
    Me.coFonteMateria.ColumnCount = 2

    For I = 2 To Plan5.Range("A1048576").End(xlUp).Row
        Me.coFonteMateria.AddItem
        Me.coFonteMateria.List(Me.coFonteMateria.ListCount - 1, 0) = Plan5.Range("C" & I).Value
        Me.coFonteMateria.List(Me.coFonteMateria.ListCount - 1, 1) = Plan5.Range("A" & I).Value
        
    Next I
  
     
'FONTE MATERIA B
    Me.coFonteMateriab.Clear
    Me.coFonteMateriab.ColumnCount = 2

    For I = 2 To Plan5.Range("A65536").End(xlUp).Row
        Me.coFonteMateriab.AddItem
        Me.coFonteMateriab.List(Me.coFonteMateriab.ListCount - 1, 0) = Plan5.Range("C" & I).Value
        Me.coFonteMateriab.List(Me.coFonteMateriab.ListCount - 1, 1) = Plan5.Range("A" & I).Value
        
    Next I
   
    
    'FONTE MATERIA 2A
    Me.coFonteMateria2a.Clear
    Me.coFonteMateria2a.ColumnCount = 2

    For I = 2 To Plan5.Range("A65536").End(xlUp).Row
        Me.coFonteMateria2a.AddItem
        Me.coFonteMateria2a.List(Me.coFonteMateria2a.ListCount - 1, 0) = Plan5.Range("E" & I).Value
        Me.coFonteMateria2a.List(Me.coFonteMateria2a.ListCount - 1, 1) = Plan5.Range("A" & I).Value
        
    Next I
   
   'FONTE MATERIA 2B
   Me.coFonteMateria2b.Clear
    Me.coFonteMateria2b.ColumnCount = 2

    For I = 2 To Plan5.Range("A65536").End(xlUp).Row
        Me.coFonteMateria2b.AddItem
        Me.coFonteMateria2b.List(Me.coFonteMateria2b.ListCount - 1, 0) = Plan5.Range("E" & I).Value
        Me.coFonteMateria2b.List(Me.coFonteMateria2b.ListCount - 1, 1) = Plan5.Range("A" & I).Value
        
    Next I
   
    
    'FONTE MATERIA 3A
    Me.coFonteMateria3a.Clear
    Me.coFonteMateria3a.ColumnCount = 2

    For I = 2 To Plan5.Range("A65536").End(xlUp).Row
        Me.coFonteMateria3a.AddItem
        Me.coFonteMateria3a.List(Me.coFonteMateria3a.ListCount - 1, 0) = Plan5.Range("G" & I).Value
        Me.coFonteMateria3a.List(Me.coFonteMateria3a.ListCount - 1, 1) = Plan5.Range("A" & I).Value
        
    Next I
   
   'FONTE MATERIA 3B
   Me.coFonteMateria3b.Clear
    Me.coFonteMateria3b.ColumnCount = 2

    For I = 2 To Plan5.Range("A65536").End(xlUp).Row
        Me.coFonteMateria3b.AddItem
        Me.coFonteMateria3b.List(Me.coFonteMateria3b.ListCount - 1, 0) = Plan5.Range("G" & I).Value
        Me.coFonteMateria3b.List(Me.coFonteMateria3b.ListCount - 1, 1) = Plan5.Range("A" & I).Value
        
    Next I
    
        
     'TEMA 2A
    Me.coTema2a.Clear
    Me.coTema2a.ColumnCount = 2

    For I = 2 To Plan5.Range("A65536").End(xlUp).Row
        Me.coTema2a.AddItem
        Me.coTema2a.List(Me.coTema2a.ListCount - 1, 0) = Plan5.Range("D" & I).Value
        Me.coTema2a.List(Me.coTema2a.ListCount - 1, 1) = Plan5.Range("A" & I).Value
        
    Next I
   
   'TEMA 2B
   Me.coTema2b.Clear
    Me.coTema2b.ColumnCount = 2

    For I = 2 To Plan5.Range("A65536").End(xlUp).Row
        Me.coTema2b.AddItem
        Me.coTema2b.List(Me.coTema2b.ListCount - 1, 0) = Plan5.Range("D" & I).Value
        Me.coTema2b.List(Me.coTema2b.ListCount - 1, 1) = Plan5.Range("A" & I).Value
        
    Next I
   
    
    'TEMA 3A
    Me.coTema3a.Clear
    Me.coTema3a.ColumnCount = 2

    For I = 2 To Plan5.Range("A65536").End(xlUp).Row
        Me.coTema3a.AddItem
        Me.coTema3a.List(Me.coTema3a.ListCount - 1, 0) = Plan5.Range("F" & I).Value
        Me.coTema3a.List(Me.coTema3a.ListCount - 1, 1) = Plan5.Range("A" & I).Value
        
    Next I
   
   'TEMA 3B
   Me.coTema3b.Clear
    Me.coTema3b.ColumnCount = 2

    For I = 2 To Plan5.Range("A65536").End(xlUp).Row
        Me.coTema3b.AddItem
        Me.coTema3b.List(Me.coTema3b.ListCount - 1, 0) = Plan5.Range("F" & I).Value
        Me.coTema3b.List(Me.coTema3b.ListCount - 1, 1) = Plan5.Range("A" & I).Value
        
    Next I
    
    'CENA 2A
    Me.coCena2.Clear
    Me.coCena2.ColumnCount = 2

    For I = 2 To Plan3.Range("A65536").End(xlUp).Row
        Me.coCena2.AddItem
        Me.coCena2.List(Me.coCena2.ListCount - 1, 0) = Plan3.Range("D" & I).Value
        Me.coCena2.List(Me.coCena2.ListCount - 1, 1) = Plan3.Range("E" & I).Value
        
    Next I
   
   'CENA 2B
   Me.coCena2b.Clear
    Me.coCena2b.ColumnCount = 2

    For I = 2 To Plan3.Range("A65536").End(xlUp).Row
        Me.coCena2b.AddItem
        Me.coCena2b.List(Me.coCena2b.ListCount - 1, 0) = Plan3.Range("D" & I).Value
        Me.coCena2b.List(Me.coCena2b.ListCount - 1, 1) = Plan3.Range("E" & I).Value
        
    Next I
   
    
    'CENA 3A
    Me.coCena3.Clear
    Me.coCena3.ColumnCount = 2

    For I = 2 To Plan3.Range("A65536").End(xlUp).Row
        Me.coCena3.AddItem
        Me.coCena3.List(Me.coCena3.ListCount - 1, 0) = Plan3.Range("D" & I).Value
        Me.coCena3.List(Me.coCena3.ListCount - 1, 1) = Plan3.Range("E" & I).Value
        
    Next I
   
   'CENA 3B
   Me.coCena3b.Clear
    Me.coCena3b.ColumnCount = 2

    For I = 2 To Plan3.Range("A65536").End(xlUp).Row
        Me.coCena3b.AddItem
        Me.coCena3b.List(Me.coCena3b.ListCount - 1, 0) = Plan3.Range("D" & I).Value
        Me.coCena3b.List(Me.coCena3b.ListCount - 1, 1) = Plan3.Range("E" & I).Value
        
    Next I
   
   'FONTE DESTAQUE
    Me.coFonteDestaque.Clear
    Me.coFonteDestaque.ColumnCount = 2

    For I = 2 To Plan5.Range("A65536").End(xlUp).Row
        Me.coFonteDestaque.AddItem
        Me.coFonteDestaque.List(Me.coFonteDestaque.ListCount - 1, 0) = Plan5.Range("B" & I).Value
        Me.coFonteDestaque.List(Me.coFonteDestaque.ListCount - 1, 1) = Plan5.Range("A" & I).Value
        
    Next I
   
   'DESTAQUE
    Me.coDestaque.Clear
    Me.coDestaque.ColumnCount = 2

    For I = 2 To Plan1.Range("A65536").End(xlUp).Row
        Me.coDestaque.AddItem
        Me.coDestaque.List(Me.coDestaque.ListCount - 1, 0) = Plan1.Range("C" & I).Value
        Me.coDestaque.List(Me.coDestaque.ListCount - 1, 1) = Plan1.Range("D" & I).Value
        
    Next I
   
   'DISCURSO 1B
    Me.ComboBox21.Clear
    Me.ComboBox21.ColumnCount = 1

    For I = 2 To Plan1.Range("A65536").End(xlUp).Row
        Me.ComboBox21.AddItem
        Me.ComboBox21.List(Me.ComboBox21.ListCount - 1, 0) = Plan8.Range("C" & I).Value
                
    Next I
   
   
   Me.Cbo_Discurso1.Clear
    Me.Cbo_Discurso1.ColumnCount = 1

    For I = 2 To Plan1.Range("A65536").End(xlUp).Row
        Me.Cbo_Discurso1.AddItem
        Me.Cbo_Discurso1.List(Me.Cbo_Discurso1.ListCount - 1, 0) = Plan8.Range("C" & I).Value
                
    Next I
   Me.chbox2a.Value = True
       
    
End Sub

Private Sub useform_mousemove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

UserForm_Initialize
End Sub
    
    

Private Sub carregaDados()
    indiceData = 2
    Call CarregaData
End Sub

Private Sub SalvaRegistro(ByVal id As Long, ByVal indice As Long)
    
    
   If Me.ChBoxSalaA.Value = "SalaA" Then
Sheets("CadPartesSalaA").Select
Range("A65000").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = txt_Data
ActiveCell.Offset(0, 1).Value = Me.Cbo_Discurso1.Text
ActiveCell.Offset(0, 2).Value = Me.ComboBox10.Text
ActiveCell.Offset(0, 3).Value = Me.coFonteMateria.Text
ActiveCell.Offset(0, 4).Value = Me.ComboBox11.Text  'discurso 2a
ActiveCell.Offset(0, 5).Value = Me.coEstudo2a
ActiveCell.Offset(0, 6).Value = Me.coFonteMateria2a.Text
ActiveCell.Offset(0, 7).Value = Me.coTema2a.Text
ActiveCell.Offset(0, 8).Value = Me.coCena2.Text
ActiveCell.Offset(0, 9).Value = Me.ComboBox7.Text
ActiveCell.Offset(0, 10).Value = Me.Cbo_Discurso3.Text
ActiveCell.Offset(0, 11).Value = Me.coEstudo3a.Text
ActiveCell.Offset(0, 12).Value = Me.coFonteMateria3a.Text
ActiveCell.Offset(0, 13).Value = Me.coTema3a.Text
ActiveCell.Offset(0, 14).Value = Me.coCena3.Text
ActiveCell.Offset(0, 15).Value = Me.ComboBox20.Text

End If

   If Me.ChBoxSalaB.Value = "SalaB" Then
Sheets("CadPartesSalaB").Select
Range("A65000").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = txt_Data
ActiveCell.Offset(0, 16).Value = Me.ComboBox21.Text
ActiveCell.Offset(0, 17).Value = Me.ComboBox23.Text
ActiveCell.Offset(0, 18).Value = Me.coFonteMateriab.Text
ActiveCell.Offset(0, 19).Value = Me.ComboBox25.Text  'discurso 2b
ActiveCell.Offset(0, 20).Value = Me.coEstudo2b.Text
ActiveCell.Offset(0, 21).Value = Me.coFonteMateria2b.Text
ActiveCell.Offset(0, 22).Value = Me.coTema2b.Text
ActiveCell.Offset(0, 23).Value = Me.coCena2b.Text
ActiveCell.Offset(0, 24).Value = Me.ComboBox22.Text
ActiveCell.Offset(0, 25).Value = Me.ComboBox30.Text 'discurso 3b
ActiveCell.Offset(0, 26).Value = Me.coEstudo3b.Text
ActiveCell.Offset(0, 27).Value = Me.coFonteMateria3b.Text
ActiveCell.Offset(0, 28).Value = Me.coTema3b.Text
ActiveCell.Offset(0, 29).Value = Me.coCena3b.Text
ActiveCell.Offset(0, 30).Value = Me.ComboBox35.Text
End If
   
If Me.coDestaque.Value = "Destaque BBB" Then
Sheets("CadDestaqueBBBB").Select
Range("A65000").End(xlUp).Offset(1, 0).Select
ActiveCell.Offset(0, 1).Value = Me.coDestaque.Text

If Me.coFonteDestaque.Value = "Fonte Materia Destaque" Then
Sheets("CadDestaqueBBB").Select
Range("A65000").End(xlUp).Offset(1, 0).Select
ActiveCell.Offset(0, 1).Value = Me.coFonteMateria.Text


End If
End If

  End Sub
  
  
  Private Sub CarregaData()
    'carrega os dados do primeiro registro
    With wsCadPartesSalaA
        If Not IsEmpty(.Cells(indiceData, colData)) Then
        
            Me.txt_Data.Text = .Cells(indiceData, colData).Value
            Me.coDestaque.Text = .Cells(indiceData, colDestaqueBBB).Value
            Me.coFonteDestaque.Text = .Cells(indiceData, colFonteMatDestaque).Value
            

        End If
    End With
    
     With wsCadPartesSalaB
        If Not IsEmpty(.Cells(indiceData, colData)) Then
        
    Me.ComboBox21.Text = Cells(indiceData, colDisc1SalaB).Value
    Me.ComboBox23.Text = .Cells(indiceData, colEst1SalaB).Value
    Me.coFonteMateriab.Text = .Cells(indiceData, colFonteMat1SalaB).Value
    Me.ComboBox25.Text = .Cells(indiceData, colDisc2SalaB).Value
    Me.coEstudo2b.Text = .Cells(indiceData, colEst2SalaB).Value
    Me.coFonteMateria2b.Text = .Cells(indiceData, colFonteMat2SalaB).Value
    Me.coTema2b.Text = .Cells(indiceData, colTema2SalaB).Value
    Me.coCena2b.Text = .Cells(indiceData, colCena2SalaB).Value
    Me.ComboBox22.Text = .Cells(indiceData, colAjudante2SalaB).Value
    Me.ComboBox30.Text = .Cells(indiceData, colDisc3SalaB).Value
    Me.coEstudo3b.Text = .Cells(indiceData, colEst3SalaB).Value
    Me.coFonteMateria3b.Text = .Cells(indiceData, colFonteMat3SalaB).Value
    Me.coTema3b.Text = .Cells(indiceData, colTema3SalaB).Value
    Me.coCena3b.Text = .Cells(indiceData, colCena3SalaB).Value
    Me.ComboBox35.Text = .Cells(indiceData, colAjudante3SalaB).Value

        End If
    
    End With
     
     With wsCadPartesSalaA
        If Not IsEmpty(.Cells(indiceData, colData)) Then
          
    Me.Cbo_Discurso1.Text = Cells(indiceData, colDisc1SalaA).Value
    Me.ComboBox10.Text = Cells(indiceData, colEst1SalaA).Value
    Me.coFonteMateria.Text = .Cells(indiceData, colFonteMat1SalaA).Value
    Me.ComboBox11.Text = .Cells(indiceData, colDisc2SalaA).Value
    Me.coEstudo2a = .Cells(indiceData, colEst2SalaA).Value
    Me.coFonteMateria2a.Text = .Cells(indiceData, colFonteMat2SalaA).Value
    Me.coTema2a.Text = .Cells(indiceData, colTema2SalaA).Value
    Me.coCena2.Text = .Cells(indiceData, colCena2SalaA).Value
    Me.ComboBox7.Text = .Cells(indiceData, colAjudante2SalaA).Value
    Me.Cbo_Discurso3.Text = .Cells(indiceData, colDisc3SalaA).Value
    Me.coEstudo3a.Text = .Cells(indiceData, colEst3SalaA).Value
    Me.coFonteMateria3a.Text = .Cells(indiceData, colFonteMat3SalaA).Value
    Me.coTema3a.Text = .Cells(indiceData, colTema3SalaA).Value
    Me.coCena3.Text = .Cells(indiceData, colCena3SalaA).Value
    Me.ComboBox20.Text = .Cells(indiceData, colAjudante3SalaA).Value

          
        
        End If
    
    End With
    Call AtualizaRegistroAtual
    
End Sub


Private Sub AtualizaRegistroAtual()
    LblData.Caption = indiceData - 2 & " de " & wsCadPartes.UsedRange.Rows.Count - 2
    'lblMensagem.Caption = ""
End Sub
Private Sub LimpaControles()

    Me.txt_Data.Text = ""
    Me.ComboBox21.Text = ""
    Me.ComboBox23.Text = ""
    Me.coFonteMateriab.Text = ""
    Me.ComboBox25.Text = ""
    Me.coEstudo2b.Text = ""
    Me.coFonteMateria2b.Text = ""
    Me.coTema2b.Text = ""
    Me.coCena2b.Text = ""
    Me.ComboBox22.Text = ""
    Me.ComboBox30.Text = ""
    Me.coEstudo3b.Text = ""
    Me.coFonteMateria3b.Text = ""
    Me.coTema3b.Text = ""
    Me.coCena3b.Text = ""
    Me.ComboBox35.Text = ""
    Me.Cbo_Discurso1.Text = ""
    Me.ComboBox10.Text = ""
    Me.coFonteMateria.Text = ""
    Me.ComboBox11.Text = ""
    Me.coEstudo2a.Text = ""
    Me.coFonteMateria2a.Text = ""
    Me.coTema2a.Text = ""
    Me.coCena2.Text = ""
    Me.ComboBox7.Text = ""
    Me.Cbo_Discurso3.Text = ""
    Me.coEstudo3a.Text = ""
    Me.coFonteMateria3a.Text = ""
    Me.coTema3a.Text = ""
    Me.coCena3.Text = ""
    Me.ComboBox20.Text = ""

End Sub
Private Sub HabilitaControles()
    Me.txt_Data.Locked = False
    Me.ComboBox21.Locked = False
    Me.ComboBox23.Locked = False
    Me.coFonteMateriab.Locked = False
    Me.ComboBox25.Locked = False
    Me.coEstudo2b.Locked = False
    Me.coFonteMateria2b.Locked = False
    Me.coTema2b.Locked = False
    Me.coCena2b.Locked = False
    Me.ComboBox22.Locked = False
    Me.ComboBox30.Locked = False
    Me.coEstudo3b.Locked = False
    Me.coFonteMateria3b.Locked = False
    Me.coTema3b.Locked = False
    Me.coCena3b.Locked = False
    Me.ComboBox35.Locked = False
    Me.Cbo_Discurso1.Locked = False
    Me.ComboBox10.Locked = False
    Me.coFonteMateria.Locked = False
    Me.ComboBox11.Locked = False
    Me.coEstudo2a.Locked = False
    Me.coFonteMateria2a.Locked = False
    Me.coTema2a.Locked = False
    Me.coCena2.Locked = False
    Me.ComboBox7.Locked = False
    Me.Cbo_Discurso3.Locked = False
    Me.coEstudo3a.Locked = False
    Me.coFonteMateria3a.Locked = False
    Me.coTema3a.Locked = False
    Me.coCena3.Locked = False
    Me.ComboBox20.Locked = False

    
    
    'altera a cor dos controles
    Me.ComboBox21.BackColor = corHabilitaTextBox
    Me.ComboBox23.BackColor = corHabilitaTextBox
    Me.coFonteMateriab.BackColor = corHabilitaTextBox
    Me.ComboBox25.BackColor = corHabilitaTextBox
    Me.coEstudo2b.BackColor = corHabilitaTextBox
    Me.coFonteMateria2b.BackColor = corHabilitaTextBox
    Me.coTema2b.BackColor = corHabilitaTextBox
    Me.coCena2b.BackColor = corHabilitaTextBox
    Me.ComboBox22.BackColor = corHabilitaTextBox
    Me.ComboBox30.BackColor = corHabilitaTextBox
    Me.coEstudo3b.BackColor = corHabilitaTextBox
    Me.coFonteMateria3b.BackColor = corHabilitaTextBox
    Me.coTema3b.BackColor = corHabilitaTextBox
    Me.coCena3b.BackColor = corHabilitaTextBox
    Me.ComboBox35.BackColor = corHabilitaTextBox
    Me.Cbo_Discurso1.BackColor = corHabilitaTextBox
    Me.ComboBox10.BackColor = corHabilitaTextBox
    Me.coFonteMateria.BackColor = corHabilitaTextBox
    Me.ComboBox11.BackColor = corHabilitaTextBox
    Me.coEstudo2a.BackColor = corHabilitaTextBox
    Me.coFonteMateria2a.BackColor = corHabilitaTextBox
    Me.coTema2a.BackColor = corHabilitaTextBox
    Me.coCena2.BackColor = corHabilitaTextBox
    Me.ComboBox7.BackColor = corHabilitaTextBox
    Me.Cbo_Discurso3.BackColor = corHabilitaTextBox
    Me.coEstudo3a.BackColor = corHabilitaTextBox
    Me.coFonteMateria3a.BackColor = corHabilitaTextBox
    Me.coTema3a.BackColor = corHabilitaTextBox
    Me.coCena3.BackColor = corHabilitaTextBox
    Me.ComboBox20.BackColor = corHabilitaTextBox
    
    
End Sub

Private Sub DesabilitaControles()

    Me.ComboBox21.Locked = True
    Me.ComboBox23.Locked = True
    Me.coFonteMateriab.Locked = True
    Me.ComboBox25.Locked = True
    Me.coEstudo2b.Locked = True
    Me.coFonteMateria2b.Locked = True
    Me.coTema2b.Locked = True
    Me.coCena2b.Locked = True
    Me.ComboBox22.Locked = True
    Me.ComboBox30.Locked = True
    Me.coEstudo3b.Locked = True
    Me.coFonteMateria3b.Locked = True
    Me.coTema3b.Locked = True
    Me.coCena3b.Locked = True
    Me.ComboBox35.Locked = True
    Me.Cbo_Discurso1.Locked = True
    Me.ComboBox10.Locked = True
    Me.coFonteMateria.Locked = True
    Me.ComboBox11.Locked = True
    Me.coEstudo2a.Locked = True
    Me.coFonteMateria2a.Locked = True
    Me.coTema2a.Locked = True
    Me.coCena2.Locked = True
    Me.ComboBox7.Locked = True
    Me.Cbo_Discurso3.Locked = True
    Me.coEstudo3a.Locked = True
    Me.coFonteMateria3a.Locked = True
    Me.coTema3a.Locked = True
    

    'altera a cor dos controles
    Me.txt_Data.BackColor = corDesabilitaTextBox
    Me.ComboBox21.BackColor = corDesabilitaTextBox
    Me.ComboBox23.BackColor = corDesabilitaTextBox
    Me.coFonteMateriab.BackColor = corDesabilitaTextBox
    Me.ComboBox25.BackColor = corDesabilitaTextBox
    Me.coEstudo2b.BackColor = corDesabilitaTextBox
    Me.coFonteMateria2b.BackColor = corDesabilitaTextBox
    Me.coTema2b.BackColor = corDesabilitaTextBox
    Me.coCena2b.BackColor = corDesabilitaTextBox
    Me.ComboBox22.BackColor = corDesabilitaTextBox
    Me.ComboBox30.BackColor = corDesabilitaTextBox
    Me.coEstudo3b.BackColor = corDesabilitaTextBox
    Me.coFonteMateria3b.BackColor = corDesabilitaTextBox
    Me.coTema3b.BackColor = corDesabilitaTextBox
    Me.coCena3b.BackColor = corDesabilitaTextBox
    Me.ComboBox35.BackColor = corDesabilitaTextBox
    Me.Cbo_Discurso1.BackColor = corDesabilitaTextBox
    Me.ComboBox10.BackColor = corDesabilitaTextBox
    Me.coFonteMateria.BackColor = corDesabilitaTextBox
    Me.ComboBox11.BackColor = corDesabilitaTextBox
    Me.coEstudo2a.BackColor = corDesabilitaTextBox
    Me.coFonteMateria2a.BackColor = corDesabilitaTextBox
    Me.coTema2a.BackColor = corDesabilitaTextBox
    Me.coCena2.BackColor = corDesabilitaTextBox
    Me.ComboBox7.BackColor = corDesabilitaTextBox
    Me.Cbo_Discurso3.BackColor = corDesabilitaTextBox
    Me.coEstudo3a.BackColor = corDesabilitaTextBox
    Me.coFonteMateria3a.BackColor = corDesabilitaTextBox
    Me.coTema3a.BackColor = corDesabilitaTextBox
    Me.coCena3.BackColor = corDesabilitaTextBox
    Me.ComboBox20.BackColor = corDesabilitaTextBox
    
    
    
End Sub

Private Sub HabilitaBotoesAlteracao()
   'habilita os botões de alteração
    Btn_alterar.Enabled = True
    Btn_excluir.Enabled = True
    Btn_novo.Enabled = True
    Btn_ok.Enabled = False
    Btn_cancelar.Enabled = False
End Sub
Private Sub DesabilitaBotoesAlteracao()
'desabilita os botões de alteração
    Btn_alterar.Enabled = False
    Btn_excluir.Enabled = False
    Btn_novo.Enabled = False
    Btn_ok.Enabled = True
    Btn_cancelar.Enabled = True
End Sub
Private Function ValidaCamposFormulario() As Boolean

If Me.txt_Data.Value = "" Then
  Me.txt_Data.SetFocus
  MsgBox " 'Data' é um campo obrigatório. ", vbOKOnly, " Campo Obrigatório "
  ValidaCamposFormulario = False
  Exit Function

ElseIf Me.ChBoxSalaA.Value = "" Then
  Me.ChBoxSalaA.SetFocus
  MsgBox " 'Escolher a Sala é Obrigatório' é um campo obrigatório.", vbOKOnly, " Campo Obrigatório "
  ValidaCamposFormulario = False
  Exit Function

ElseIf Me.ChBoxSalaB.Value = "" Then
  Me.ChBoxSalaB.SetFocus
  MsgBox " 'Escolher a Sala é Obrigatório' é um campo obrigatório.", vbOKOnly, " Campo Obrigatório "
  ValidaCamposFormulario = False
  Exit Function


End If
ValidaCamposFormulario = True
End Function

Sub limpaMensagem()
lblMensagem.Caption = ""
End Sub

CASO POSSAM AJUDAR AGRADEÇO.

O PROJETO RODA, SOMENTE NÃO CONSIGO GRAVAR PARA AS PLANILHAS RESPECTIVAS

OBRIGADO

BETO FILMES

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.