Hello, Is there another way of using the user form and still be able to edit the excel sheet besides the
?
I get an error when I use that on my code. when I add the False statment it will mess up where the userform data needs to be saved.
thisworkbook code is this:
and the userform code is this:
There are 2 buttons that do the same thing in it.
best regards,
eLy
Code:
userform1.show false
I get an error when I use that on my code. when I add the False statment it will mess up where the userform data needs to be saved.
thisworkbook code is this:
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)'Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim ws As Worksheet
'Application.ScreenUpdating = False
Set ws = ActiveSheet
If Not Application.Intersect(Target, Range("AS7:AS32")) Is Nothing Then
sheets("FJaneiro").Activate
UserForm1.tbNumero = Cells(Target.Row, 3)
UserForm1.tbNome = Cells(Target.Row, 4)
UserForm1.tbTelefone = Cells(Target.Row, 5)
UserForm1.tbTelemovel = Cells(Target.Row, 6)
UserForm1.tbEmail = Cells(Target.Row, 7)
UserForm1.lblRow = Target.Row
UserForm1.Show
End If
ws.Activate
Set ws = ActiveSheet
If Not Application.Intersect(Target, Range("AS48:AS73")) Is Nothing Then
sheets("FFevereiro").Activate
UserForm1.tbNumero = Cells(Target.Row, 3)
UserForm1.tbNome = Cells(Target.Row, 4)
UserForm1.tbTelefone = Cells(Target.Row, 5)
UserForm1.tbTelemovel = Cells(Target.Row, 6)
UserForm1.tbEmail = Cells(Target.Row, 7)
UserForm1.lblRow = Target.Row
UserForm1.Show
End If
ws.Activate
Set ws = ActiveSheet
If Not Application.Intersect(Target, Range("AS88:AS113")) Is Nothing Then
sheets("FMarco").Activate
UserForm1.tbNumero = Cells(Target.Row, 3)
UserForm1.tbNome = Cells(Target.Row, 4)
UserForm1.tbTelefone = Cells(Target.Row, 5)
UserForm1.tbTelemovel = Cells(Target.Row, 6)
UserForm1.tbEmail = Cells(Target.Row, 7)
UserForm1.lblRow = Target.Row
UserForm1.Show
End If
ws.Activate
Set ws = ActiveSheet
If Not Application.Intersect(Target, Range("AS128:AS153")) Is Nothing Then
sheets("FAbril").Activate
UserForm1.tbNumero = Cells(Target.Row, 3)
UserForm1.tbNome = Cells(Target.Row, 4)
UserForm1.tbTelefone = Cells(Target.Row, 5)
UserForm1.tbTelemovel = Cells(Target.Row, 6)
UserForm1.tbEmail = Cells(Target.Row, 7)
UserForm1.lblRow = Target.Row
UserForm1.Show
End If
ws.Activate
Set ws = ActiveSheet
If Not Application.Intersect(Target, Range("AS168:AS193")) Is Nothing Then
sheets("FMaio").Activate
UserForm1.tbNumero = Cells(Target.Row, 3)
UserForm1.tbNome = Cells(Target.Row, 4)
UserForm1.tbTelefone = Cells(Target.Row, 5)
UserForm1.tbTelemovel = Cells(Target.Row, 6)
UserForm1.tbEmail = Cells(Target.Row, 7)
UserForm1.lblRow = Target.Row
UserForm1.Show
End If
ws.Activate
Set ws = ActiveSheet
If Not Application.Intersect(Target, Range("AS208:AS233")) Is Nothing Then
sheets("FJunho").Activate
UserForm1.tbNumero = Cells(Target.Row, 3)
UserForm1.tbNome = Cells(Target.Row, 4)
UserForm1.tbTelefone = Cells(Target.Row, 5)
UserForm1.tbTelemovel = Cells(Target.Row, 6)
UserForm1.tbEmail = Cells(Target.Row, 7)
UserForm1.lblRow = Target.Row
UserForm1.Show
End If
ws.Activate
Set ws = ActiveSheet
If Not Application.Intersect(Target, Range("AS248:AS273")) Is Nothing Then
sheets("FJulho").Activate
UserForm1.tbNumero = Cells(Target.Row, 3)
UserForm1.tbNome = Cells(Target.Row, 4)
UserForm1.tbTelefone = Cells(Target.Row, 5)
UserForm1.tbTelemovel = Cells(Target.Row, 6)
UserForm1.tbEmail = Cells(Target.Row, 7)
UserForm1.lblRow = Target.Row
UserForm1.Show
End If
ws.Activate
Set ws = ActiveSheet
If Not Application.Intersect(Target, Range("AS288:AS313")) Is Nothing Then
sheets("FAgosto").Activate
UserForm1.tbNumero = Cells(Target.Row, 3)
UserForm1.tbNome = Cells(Target.Row, 4)
UserForm1.tbTelefone = Cells(Target.Row, 5)
UserForm1.tbTelemovel = Cells(Target.Row, 6)
UserForm1.tbEmail = Cells(Target.Row, 7)
UserForm1.lblRow = Target.Row
UserForm1.Show
End If
ws.Activate
Set ws = ActiveSheet
If Not Application.Intersect(Target, Range("AS328:AS353")) Is Nothing Then
sheets("FSetembro").Activate
UserForm1.tbNumero = Cells(Target.Row, 3)
UserForm1.tbNome = Cells(Target.Row, 4)
UserForm1.tbTelefone = Cells(Target.Row, 5)
UserForm1.tbTelemovel = Cells(Target.Row, 6)
UserForm1.tbEmail = Cells(Target.Row, 7)
UserForm1.lblRow = Target.Row
UserForm1.Show
End If
ws.Activate
Set ws = ActiveSheet
If Not Application.Intersect(Target, Range("AS368:AS393")) Is Nothing Then
sheets("FOutubro").Activate
UserForm1.tbNumero = Cells(Target.Row, 3)
UserForm1.tbNome = Cells(Target.Row, 4)
UserForm1.tbTelefone = Cells(Target.Row, 5)
UserForm1.tbTelemovel = Cells(Target.Row, 6)
UserForm1.tbEmail = Cells(Target.Row, 7)
UserForm1.lblRow = Target.Row
UserForm1.Show
End If
ws.Activate
Set ws = ActiveSheet
If Not Application.Intersect(Target, Range("AS408:AS433")) Is Nothing Then
sheets("FNovembro").Activate
UserForm1.tbNumero = Cells(Target.Row, 3)
UserForm1.tbNome = Cells(Target.Row, 4)
UserForm1.tbTelefone = Cells(Target.Row, 5)
UserForm1.tbTelemovel = Cells(Target.Row, 6)
UserForm1.tbEmail = Cells(Target.Row, 7)
UserForm1.lblRow = Target.Row
UserForm1.Show
End If
ws.Activate
Set ws = ActiveSheet
If Not Application.Intersect(Target, Range("AS448:AS473")) Is Nothing Then
sheets("FDezembro").Activate
UserForm1.tbNumero = Cells(Target.Row, 3)
UserForm1.tbNome = Cells(Target.Row, 4)
UserForm1.tbTelefone = Cells(Target.Row, 5)
UserForm1.tbTelemovel = Cells(Target.Row, 6)
UserForm1.tbEmail = Cells(Target.Row, 7)
UserForm1.lblRow = Target.Row
UserForm1.Show
End If
ws.Activate
'Application.ScreenUpdating = True
End Sub
and the userform code is this:
Code:
Private Sub UserForm_Initialize()
Me.StartUpPosition = 0
Me.Top = (Application.Height - Me.Height) / 2
Me.Left = (Application.Width - Me.Width - 500)
tbNumero.SetFocus
End Sub
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Application.ScreenUpdating = False
Set ws = ActiveSheet
x = Me.lblRow 'current row
ws.Cells(x, 3).Value = tbNumero
ws.Cells(x, 4).Value = tbNome
ws.Cells(x, 5).Value = tbTelefone
ws.Cells(x, 6).Value = tbTelemovel
ws.Cells(x, 7).Value = tbEmail
Me.Hide
ws.Activate
Application.ScreenUpdating = True
End Sub
Private Sub CommandButton2_Click()
Me.Hide
End Sub
Private Sub CommandButton3_Click()
UserForm1.tbNumero.Value = ""
UserForm1.tbNome.Value = ""
UserForm1.tbTelefone.Value = ""
UserForm1.tbTelemovel.Value = ""
UserForm1.tbEmail.Value = ""
End Sub
Private Sub CommandButton4_Click()
Dim ws As Worksheet
Application.ScreenUpdating = False
Set ws = ActiveSheet
x = Me.lblRow 'current row
ws.Cells(x, 3).Value = tbNumero
ws.Cells(x, 4).Value = tbNome
ws.Cells(x, 5).Value = tbTelefone
ws.Cells(x, 6).Value = tbTelemovel
ws.Cells(x, 7).Value = tbEmail
Me.Hide
ws.Activate
Application.ScreenUpdating = True
End Sub
There are 2 buttons that do the same thing in it.
best regards,
eLy
Last edited: