Run-time error 91

M

miami2k

Board Regular
Joined
Nov 1, 2017
Messages
58
Dear All,
When I debug my code and try o restart a Userform I get this message...
runtime error 91 object variable or with block variable not set

I close and save, reopen the file and everything works fine until I have to debug again for any reason. So I have to keep saving, closing and restarting.

I've run a search in the project and I used "with" only in two ways in my code, here they are:

===============================

VBA Code: 
Sub PopolaCompl()

Dim rng As Range
Dim Cel1 As Range
Dim LR As Long
Dim ws As Worksheet
Set ws = Sheets("Filed")
With ws

LR = .Cells(.Rows.Count, "B").End(xlUp).Row

Scadenziario.ListBox2.Clear

If LR = 1 Then GoTo Finished Else

Set rng = .Range("A1:A" & LR).SpecialCells(xlCellTypeVisible)
With Scadenziario.ListBox2

  .ColumnCount = 9
  .ColumnWidths = "0;180;120;60;60;60;60;270;60;"

For Each Cel1 In rng
.AddItem CStr(Cel1.Value)
.List(.ListCount - 1, 0) = Cel1.Offset(0, 0).Value
.List(.ListCount - 1, 1) = Cel1.Offset(0, 1).Value
.List(.ListCount - 1, 2) = Cel1.Offset(0, 2).Value
.List(.ListCount - 1, 3) = Cel1.Offset(0, 3).Value
.List(.ListCount - 1, 4) = Cel1.Offset(0, 4).Value
.List(.ListCount - 1, 5) = Cel1.Offset(0, 5).Value
.List(.ListCount - 1, 6) = Cel1.Offset(0, 6).Value
.List(.ListCount - 1, 7) = Cel1.Offset(0, 7).Value
.List(.ListCount - 1, 8) = Cel1.Offset(0, 8).Value
Next Cel1
End With
End With

Finished:

End Sub

=================================
and ...
=================================

VBA Code: 
Private Sub CommandButton10_Click()

With Scadenziario
    .ComboBox13.ListIndex = 0
End With

End Sub

==========================

Any tip to solve the issue?


Thank you,


miami2k
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
do you use OPTION EXPLICIT
 
Upvote 0
Hi,
try this update to your code & see if helps

VBA Code: 
Sub PopolaCompl()
   
    Dim rng As Range
    Dim Cel1 As Range
    Dim LR As Long
    Dim ws As Worksheet
   
    Set ws = ThisWorkbook.Worksheets("Filed")
   
    With ws
        LR = .Cells(.Rows.Count, "B").End(xlUp).Row
        If LR = 1 Then GoTo Finished
        Set rng = .Range("A1:A" & LR).SpecialCells(xlCellTypeVisible)
    End With
   
   
    With Me.ListBox2
        .Clear
        .ColumnCount = 9
        .ColumnWidths = "0;180;120;60;60;60;60;270;60;"
       
        For Each Cel1 In rng
            .AddItem CStr(Cel1.Value)
            .List(.ListCount - 1, 0) = Cel1.Offset(0, 0).Value
            .List(.ListCount - 1, 1) = Cel1.Offset(0, 1).Value
            .List(.ListCount - 1, 2) = Cel1.Offset(0, 2).Value
            .List(.ListCount - 1, 3) = Cel1.Offset(0, 3).Value
            .List(.ListCount - 1, 4) = Cel1.Offset(0, 4).Value
            .List(.ListCount - 1, 5) = Cel1.Offset(0, 5).Value
            .List(.ListCount - 1, 6) = Cel1.Offset(0, 6).Value
            .List(.ListCount - 1, 7) = Cel1.Offset(0, 7).Value
            .List(.ListCount - 1, 8) = Cel1.Offset(0, 8).Value
        Next Cel1
    End With
       
Finished:

End Sub

Dave
 
Last edited:
Upvote 0
mole999 said:
do you use OPTION EXPLICIT
Click to expand...
I've put "Option Explicit" at the beginning of one Module where I declare all the public variables.
My Excel is in Italian, at the top it says General on the left and Declarations on the right.
Should I mention it somewhere else?
 

Attachments

  • Capture.PNG
    Capture.PNG
    11.6 KB · Views: 3
Upvote 0
dmt32 said:
Hi,
try this update to your code & see if helps

VBA Code: 
Sub PopolaCompl()
  
    Dim rng As Range
    Dim Cel1 As Range
    Dim LR As Long
    Dim ws As Worksheet
  
    Set ws = ThisWorkbook.Worksheets("Filed")
  
    With ws
        LR = .Cells(.Rows.Count, "B").End(xlUp).Row
        If LR = 1 Then GoTo Finished
        Set rng = .Range("A1:A" & LR).SpecialCells(xlCellTypeVisible)
    End With
  
  
    With Me.ListBox2
        .Clear
        .ColumnCount = 9
        .ColumnWidths = "0;180;120;60;60;60;60;270;60;"
      
        For Each Cel1 In rng
            .AddItem CStr(Cel1.Value)
            .List(.ListCount - 1, 0) = Cel1.Offset(0, 0).Value
            .List(.ListCount - 1, 1) = Cel1.Offset(0, 1).Value
            .List(.ListCount - 1, 2) = Cel1.Offset(0, 2).Value
            .List(.ListCount - 1, 3) = Cel1.Offset(0, 3).Value
            .List(.ListCount - 1, 4) = Cel1.Offset(0, 4).Value
            .List(.ListCount - 1, 5) = Cel1.Offset(0, 5).Value
            .List(.ListCount - 1, 6) = Cel1.Offset(0, 6).Value
            .List(.ListCount - 1, 7) = Cel1.Offset(0, 7).Value
            .List(.ListCount - 1, 8) = Cel1.Offset(0, 8).Value
        Next Cel1
    End With
      
Finished:

End Sub

Dave
Click to expand...


I've tried but the option me. is not accepted because this code is saved on a module and the sub is called by the code so I think I need to tell the system to which userform I refer to because it is not necessarily active.
 
Upvote 0
miami2k said:
I've tried but the option me. is not accepted because this code is saved on a module and the sub is called by the code so I think I need to tell the system to which userform I refer to because it is not necessarily active.
Click to expand...

Sorry, I assumed code was in UserForms code page - Just replace ME keyword with your forms name or you could pass the form object as an argument to your code.
Main thing is, did that changes help or not?

Dave
 
Upvote 0
dmt32 said:
Sorry, I assumed code was in UserForms code page - Just replace ME keyword with your forms name or you could pass the form object as an argument to your code.
Main thing is, did that changes help or not?

Dave
Click to expand...
Still getting the same error for now and the problem is that it does not say which is the WITH giving the problem. I have more than one in the code even if the syntax is the one I gave.
Now I will try to delete them and see what happens.
 
Upvote 0
miami2k said:
Still getting the same error for now and the problem is that it does not say which is the WITH giving the problem. I have more than one in the code even if the syntax is the one I gave.
Now I will try to delete them and see what happens.
Click to expand...

To help determine your issue, it would be helpful to forum if you publish ALL the userform code .

Dave
 
Upvote 0
Private Sub Cmd_Oggi_Click()
Me.TextBox_Data = Date
End Sub

Private Sub CmdButton_Add_Click()

Dim ssheet As Worksheet

'If Intro
If TextBox_Data.Value = "" Or Combo_Utente.Value = "" Or Combo_Cliente.Value = "" Then
Message = "Client missing"
risposta = MsgBox(Message, Style)
Exit Sub
End If

'If almeno una attivita o spesa
If Combo_Attività.Value = "" And ComboBox9 = "" Then
Message = "Add Activity or Expenses"
risposta = MsgBox(Message, Style)
Exit Sub
End If

'If sub non puo essere vuota se activity non é vuota
If Me.Combo_Attività.ListIndex > -1 And ComboBox4 = "" Then
MsgBox ("Subactivity Missing")
Exit Sub
End If

If Me.ComboBox5.ListIndex > -1 And ComboBox6 = "" Then
MsgBox ("Subactivity Missing")
Exit Sub
End If

If Me.ComboBox7.ListIndex > -1 And ComboBox8 = "" Then
MsgBox ("Subactivity Missing")
Exit Sub
End If

'If mancano minuti
If Me.Combo_Attività.ListIndex > -1 And TextBox_Minuti.Value = 0 Then
MsgBox ("Minutes Missing")
Exit Sub
End If

If Me.ComboBox5.ListIndex > -1 And TextBox2.Value = 0 Then
MsgBox ("Minutes Missing")
Exit Sub
End If

If Me.ComboBox7.ListIndex > -1 And TextBox5.Value = 0 Then
MsgBox ("Minutes Missing")
Exit Sub
End If

'If manca importo spese
If Me.ComboBox9.ListIndex > -1 And TextBox13 = "" Then
Message = "Expenses Amount Missing"
risposta = MsgBox(Message, Style)
Exit Sub
End If

If Me.ComboBox10.ListIndex > -1 And TextBox14 = "" Then
Message = "Expenses Amount Missing"
risposta = MsgBox(Message, Style)
Exit Sub
End If

If Me.ComboBox10.ListIndex > -1 And TextBox15 = "" Then
Message = "Expenses Amount Missing"
risposta = MsgBox(Message, Style)
Exit Sub
End If

'Impo data
TextBox_Data.Value = Format(TextBox_Data.Value, "dd/mm/yyyy")
TextBox_Data.Value = CDate(TextBox_Data)

'Inserisci dati
Set ssheet = ThisWorkbook.Sheets("TimeSheet")

Sheets("TimeSheet").Select

'AZZERA FILTRI
On Error Resume Next
ssheet.ShowAllData
On Error GoTo 0

Dim ID As Integer
LastRow = Sheets("TimeSheet").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:J" & LastRow).Sort key1:=Range("A2:A" & LastRow), _
order1:=xlDescending, Header:=xlNo

nr = 2

If Me.Combo_Attività = "" Then GoTo Spese
Range("A2").EntireRow.Insert
ID = ssheet.Cells(nr + 1, 1)
ssheet.Cells(nr, 1) = ID + 1
ssheet.Cells(nr, 2) = Me.Combo_Utente
ssheet.Cells(nr, 3) = DateValue(TextBox_Data.Value)
ssheet.Cells(nr, 4) = Me.Combo_Cliente
ssheet.Cells(nr, 5) = Me.Combo_Attività
ssheet.Cells(nr, 6) = Me.ComboBox4
ssheet.Cells(nr, 7) = Me.TextBox_Descrizione
ssheet.Cells(nr, 8) = Val(Me.TextBox_Minuti)
ssheet.Cells(nr, 9) = Val(Me.ComboBox15)
ssheet.Cells(nr, 10) = Val(Me.TextBox9)

If Me.ComboBox5 = "" Then GoTo Spese

Range("A2").EntireRow.Insert
ID = ssheet.Cells(nr + 1, 1)
ssheet.Cells(nr, 1) = ID + 1
ssheet.Cells(nr, 2) = Me.Combo_Utente
ssheet.Cells(nr, 3) = DateValue(TextBox_Data.Value)
ssheet.Cells(nr, 4) = Me.Combo_Cliente
ssheet.Cells(nr, 5) = Me.ComboBox5
ssheet.Cells(nr, 6) = Me.ComboBox6
ssheet.Cells(nr, 7) = Me.TextBox1
ssheet.Cells(nr, 8) = Val(Me.TextBox2)
ssheet.Cells(nr, 9) = Val(Me.ComboBox16)
ssheet.Cells(nr, 10) = Val(Me.TextBox10)

If Me.ComboBox7 = "" Then GoTo Spese

Range("A2").EntireRow.Insert
ID = ssheet.Cells(nr + 1, 1)
ssheet.Cells(nr, 1) = ID + 1
ssheet.Cells(nr, 2) = Me.Combo_Utente
ssheet.Cells(nr, 3) = DateValue(TextBox_Data.Value)
ssheet.Cells(nr, 4) = Me.Combo_Cliente
ssheet.Cells(nr, 5) = Me.ComboBox7
ssheet.Cells(nr, 6) = Me.ComboBox8
ssheet.Cells(nr, 7) = Me.TextBox4
ssheet.Cells(nr, 8) = Val(Me.TextBox5)
ssheet.Cells(nr, 9) = Val(Me.ComboBox17)
ssheet.Cells(nr, 10) = Val(Me.TextBox11)

Spese:

Range("A2").EntireRow.Insert
ID = ssheet.Cells(nr + 1, 1)
ssheet.Cells(nr, 1) = ID + 1
ssheet.Cells(nr, 2) = Me.Combo_Utente
ssheet.Cells(nr, 3) = DateValue(TextBox_Data.Value)
ssheet.Cells(nr, 4) = Me.Combo_Cliente
ssheet.Cells(nr, 5) = "Expenses"
ssheet.Cells(nr, 6) = Me.ComboBox9
ssheet.Cells(nr, 7) = Me.TextBox7
ssheet.Cells(nr, 8) = ""
ssheet.Cells(nr, 9) = ""
ssheet.Cells(nr, 10) = Val(Me.TextBox13)

If Me.ComboBox10 = "" Then GoTo Finito

Range("A2").EntireRow.Insert
ID = ssheet.Cells(nr + 1, 1)
ssheet.Cells(nr, 1) = ID + 1
ssheet.Cells(nr, 2) = Me.Combo_Utente
ssheet.Cells(nr, 3) = DateValue(TextBox_Data.Value)
ssheet.Cells(nr, 4) = Me.Combo_Cliente
ssheet.Cells(nr, 5) = "Expenses"
ssheet.Cells(nr, 6) = Me.ComboBox10
ssheet.Cells(nr, 7) = Me.TextBox8
ssheet.Cells(nr, 8) = ""
ssheet.Cells(nr, 9) = ""
ssheet.Cells(nr, 10) = Val(Me.TextBox14)

If Me.ComboBox11 = "" Then GoTo Finito

Range("A2").EntireRow.Insert
ID = ssheet.Cells(nr + 1, 1)
ssheet.Cells(nr, 1) = ID + 1
ssheet.Cells(nr, 2) = Me.Combo_Utente
ssheet.Cells(nr, 3) = DateValue(TextBox_Data.Value)
ssheet.Cells(nr, 4) = Me.Combo_Cliente
ssheet.Cells(nr, 5) = "Expenses"
ssheet.Cells(nr, 6) = Me.ComboBox11
ssheet.Cells(nr, 7) = Me.TextBox12
ssheet.Cells(nr, 8) = ""
ssheet.Cells(nr, 9) = ""
ssheet.Cells(nr, 10) = Val(Me.TextBox15)

Finito:

'Ripulisci

Combo_Cliente.Text = ""
Me.TextBox_Data = Date
Combo_Utente.ListIndex = Sheets("Workings").Cells(2, 7).Value - 1

Combo_Attività.Text = ""
ComboBox4.Text = ""
Me.TextBox_Descrizione.Text = ""
Me.TextBox_Minuti.Text = ""
ComboBox15.Text = ""
Me.TextBox9.Text = ""

ComboBox5.Text = ""
ComboBox6.Text = ""
Me.TextBox1.Text = ""
Me.TextBox2.Text = ""
Me.ComboBox16.Text = ""
Me.TextBox10.Text = ""

ComboBox7.Text = ""
ComboBox8.Text = ""
Me.TextBox4.Text = ""
Me.TextBox5.Text = ""
Me.ComboBox17.Text = ""
Me.TextBox11.Text = ""

Me.ComboBox9.Text = ""
Me.TextBox7.Text = ""
Me.TextBox13.Text = ""

Me.ComboBox10.Text = ""
Me.TextBox8.Text = ""
Me.TextBox14.Text = ""

Me.ComboBox11.Text = ""
Me.TextBox12.Text = ""
Me.TextBox15.Text = ""

Me.ComboBox5.Enabled = False
Me.ComboBox7.Enabled = False
Me.ComboBox10.Enabled = False
Me.ComboBox11.Enabled = False

Sheets("SCFI").Select



ActiveWorkbook.Save

End Sub

Private Sub ComboBox1_Change()

Set Company = Me.ComboBox1

'Filtro
Sheets("Due").Select

rigusate = Sheets("Due").Cells(Rows.Count, 1).End(xlUp).Row + 1

Sheets("Due").Range("A1", "H" & rigusate).AutoFilter Field:=2, Criteria1:=Company

ListBox1.Clear

Ripopola:
Call Ripopola

End Sub

Private Sub ComboBox10_Change()

ComboBox11.Enabled = True

End Sub

Private Sub ComboBox12_Change()
Set Company = Me.ComboBox12

'Filtro
Sheets("Due").Select

rigusate = Sheets("Filed").Cells(Rows.Count, 1).End(xlUp).Row + 1

Sheets("Filed").Range("A1", "H" & rigusate).AutoFilter Field:=2, Criteria1:=Company

ListBox2.Clear

Ripopola:
Call PopolaCompl
End Sub

Private Sub ComboBox13_Change()

Set Activity = Me.ComboBox13

'Filtro
Sheets("Due").Select

rigusate = Sheets("Filed").Cells(Rows.Count, 1).End(xlUp).Row + 1

Sheets("Filed").Range("A1", "H" & rigusate).AutoFilter Field:=3, Criteria1:=Activity

ListBox2.Clear

Ripopola:
Call PopolaCompl

End Sub

Private Sub ComboBox14_Change()

Set User = Me.ComboBox14

'Filtro
Sheets("Due").Select

rigusate = Sheets("Filed").Cells(Rows.Count, 1).End(xlUp).Row + 1

Sheets("Filed").Range("A1", "H" & rigusate).AutoFilter Field:=7, Criteria1:=User

ListBox2.Clear

Ripopola:
Call PopolaCompl

End Sub

Private Sub ComboBox15_Change()

Call UpdateSumTS

End Sub

Private Sub ComboBox16_Change()

Call UpdateSumTS

End Sub

Private Sub ComboBox17_Change()

Call UpdateSumTS

End Sub

Private Sub ComboBox2_Change()

Set Activity = Me.ComboBox2

'Filtro
Sheets("Due").Select

rigusate = Sheets("Due").Cells(Rows.Count, 1).End(xlUp).Row + 1

Sheets("Due").Range("A1", "H" & rigusate).AutoFilter Field:=3, Criteria1:=Activity

ListBox1.Clear

Ripopola:
Call Ripopola

End Sub

Private Sub ComboBox3_Change()

Set User = Me.ComboBox3

'Filtro
Sheets("Due").Select

rigusate = Sheets("Due").Cells(Rows.Count, 1).End(xlUp).Row + 1

Sheets("Due").Range("A1", "H" & rigusate).AutoFilter Field:=7, Criteria1:=User

ListBox1.Clear

Ripopola:
Call Ripopola

End Sub

Private Sub Combo_Attività_Change()

Set Activity = Me.Combo_Attività

Me.ComboBox4.Clear

If Activity = "Accounting" Then
Me.ComboBox4.List = AccList.Value
Me.ComboBox15.ListIndex = 1
End If
If Activity = "Administrative" Then
Me.ComboBox4.List = AdminList.Value
Me.ComboBox15.ListIndex = 0
End If
If Activity = "Consulting" Then
Me.ComboBox4.List = ConsList.Value
Me.ComboBox15.ListIndex = 2
End If
If Activity = "Payments" Then
Me.ComboBox4.List = PaymList.Value
Me.ComboBox15.ListIndex = 0
End If

ComboBox5.Enabled = True

End Sub

Private Sub ComboBox5_Change()

Set Activity = Me.ComboBox5

Me.ComboBox6.Clear

If Activity = "Accounting" Then
Me.ComboBox6.List = AccList.Value
Me.ComboBox16.ListIndex = 1
End If
If Activity = "Administrative" Then
Me.ComboBox6.List = AdminList.Value
Me.ComboBox16.ListIndex = 0
End If
If Activity = "Consulting" Then
Me.ComboBox6.List = ConsList.Value
Me.ComboBox16.ListIndex = 2
End If
If Activity = "Payments" Then
Me.ComboBox6.List = PaymList.Value
Me.ComboBox16.ListIndex = 0
End If

ComboBox7.Enabled = True

End Sub

Private Sub ComboBox7_Change()

Set Activity = Me.ComboBox7

Me.ComboBox8.Clear

If Activity = "Accounting" Then
Me.ComboBox8.List = AccList.Value
Me.ComboBox17.ListIndex = 1
End If
If Activity = "Administrative" Then
Me.ComboBox8.List = AdminList.Value
Me.ComboBox17.ListIndex = 0
End If
If Activity = "Consulting" Then
Me.ComboBox8.List = ConsList.Value
Me.ComboBox17.ListIndex = 2
End If
If Activity = "Payments" Then
Me.ComboBox8.List = PaymList.Value
Me.ComboBox17.ListIndex = 0
End If

End Sub

Private Sub ComboBox9_Change()

ComboBox10.Enabled = True

End Sub

Private Sub CommandButton1_Click()

Sheets("Due").Select

Me.ComboBox1.Value = Null
Me.ComboBox2.Value = Null
Me.ComboBox3.Value = Null

On Error Resume Next
Sheets("Due").ShowAllData

Call Ripopola

End Sub

Private Sub CommandButton10_Click()

With Scadenziario
.ComboBox13.ListIndex = 0
End With

End Sub

Private Sub CommandButton11_Click()

With Scadenziario
.ComboBox14.ListIndex = 0
End With

End Sub

Private Sub CommandButton12_Click()

With Scadenziario
.ComboBox12.ListIndex = 0
.ComboBox13.ListIndex = 0
.ComboBox14.ListIndex = 0
End With

End Sub

Private Sub CommandButton13_Click()

Sheets("TimeSheet").Select

LastRow = Sheets("TimeSheet").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:J" & LastRow).Sort key1:=Range("B2:B" & LastRow), _
order1:=xlDescending, Header:=xlNo

Call PopolaTimesheet

EditTimesheet.Show

End Sub

Private Sub CommandButton2_Click()

Me.ComboBox1.Value = Null

rigusate = Sheets("Due").Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Due").Range("A1", "I" & rigusate).AutoFilter Field:=2

Call Ripopola

End Sub

Private Sub CommandButton3_Click()

Me.ComboBox2.Value = Null

rigusate = Sheets("Due").Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Due").Range("A1", "I" & rigusate).AutoFilter Field:=3

Call Ripopola

End Sub

Private Sub CommandButton4_Click()

Me.ComboBox2.Value = Null

rigusate = Sheets("Due").Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Due").Range("A1", "I" & rigusate).AutoFilter Field:=7

Call Ripopola

End Sub


Private Sub Label12_Click()

Sheets("Due").Select

LastRow = Sheets("Due").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:H" & LastRow).Sort key1:=Range("F2:F" & LastRow), _
order1:=xlAscending, Header:=xlNo

Call Ripopola

End Sub


Private Sub CommandButton6_Click()

CreateNew.Show

End Sub

Private Sub CommandButton9_Click()

With Scadenziario
.ComboBox12.ListIndex = 0
End With

End Sub



Private Sub Expenses_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Ripulisci

Combo_Cliente.Text = ""
Me.TextBox_Data = Date
Combo_Utente.ListIndex = Sheets("Workings").Cells(2, 7).Value - 1

Combo_Attività.Text = ""
ComboBox4.Text = ""
Me.TextBox_Descrizione.Text = ""
Me.TextBox_Minuti.Text = ""
ComboBox15.Text = ""
Me.TextBox9.Text = ""

ComboBox5.Text = ""
ComboBox6.Text = ""
Me.TextBox1.Text = ""
Me.TextBox2.Text = ""
Me.ComboBox16.Text = ""
Me.TextBox10.Text = ""

ComboBox7.Text = ""
ComboBox8.Text = ""
Me.TextBox4.Text = ""
Me.TextBox5.Text = ""
Me.ComboBox17.Text = ""
Me.TextBox11.Text = ""

Me.ComboBox9.Text = ""
Me.TextBox7.Text = ""
Me.TextBox13.Text = ""

Me.ComboBox10.Text = ""
Me.TextBox8.Text = ""
Me.TextBox14.Text = ""

Me.ComboBox11.Text = ""
Me.TextBox12.Text = ""
Me.TextBox15.Text = ""
End Sub

Private Sub Frame1_Click()

End Sub

Private Sub Frame5_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Ripulisci

Combo_Cliente.Text = ""
Me.TextBox_Data = Date
Combo_Utente.ListIndex = Sheets("Workings").Cells(2, 7).Value

Combo_Attività.Text = ""
ComboBox4.Text = ""
Me.TextBox_Descrizione.Text = ""
Me.TextBox_Minuti.Text = ""
ComboBox15.Text = ""
Me.TextBox9.Text = ""

ComboBox5.Text = ""
ComboBox6.Text = ""
Me.TextBox1.Text = ""
Me.TextBox2.Text = ""
Me.ComboBox16.Text = ""
Me.TextBox10.Text = ""

ComboBox7.Text = ""
ComboBox8.Text = ""
Me.TextBox4.Text = ""
Me.TextBox5.Text = ""
Me.ComboBox17.Text = ""
Me.TextBox11.Text = ""

Me.ComboBox9.Text = ""
Me.TextBox7.Text = ""
Me.TextBox13.Text = ""

Me.ComboBox10.Text = ""
Me.TextBox8.Text = ""
Me.TextBox14.Text = ""

Me.ComboBox11.Text = ""
Me.TextBox12.Text = ""
Me.TextBox15.Text = ""
End Sub

Private Sub Frame6_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Ripulisci

Combo_Cliente.Text = ""
Me.TextBox_Data = Date
Combo_Utente.ListIndex = Sheets("Workings").Cells(2, 7).Value - 1

Combo_Attività.Text = ""
ComboBox4.Text = ""
Me.TextBox_Descrizione.Text = ""
Me.TextBox_Minuti.Text = ""
ComboBox15.Text = ""
Me.TextBox9.Text = ""

ComboBox5.Text = ""
ComboBox6.Text = ""
Me.TextBox1.Text = ""
Me.TextBox2.Text = ""
Me.ComboBox16.Text = ""
Me.TextBox10.Text = ""

ComboBox7.Text = ""
ComboBox8.Text = ""
Me.TextBox4.Text = ""
Me.TextBox5.Text = ""
Me.ComboBox17.Text = ""
Me.TextBox11.Text = ""

Me.ComboBox9.Text = ""
Me.TextBox7.Text = ""
Me.TextBox13.Text = ""

Me.ComboBox10.Text = ""
Me.TextBox8.Text = ""
Me.TextBox14.Text = ""

Me.ComboBox11.Text = ""
Me.TextBox12.Text = ""
Me.TextBox15.Text = ""
End Sub



Private Sub Frame8_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Ripulisci

Combo_Cliente.Text = ""
Me.TextBox_Data = Date
Combo_Utente.ListIndex = Sheets("Workings").Cells(2, 7).Value

Combo_Attività.Text = ""
ComboBox4.Text = ""
Me.TextBox_Descrizione.Text = ""
Me.TextBox_Minuti.Text = ""
ComboBox15.Text = ""
Me.TextBox9.Text = ""

ComboBox5.Text = ""
ComboBox6.Text = ""
Me.TextBox1.Text = ""
Me.TextBox2.Text = ""
Me.ComboBox16.Text = ""
Me.TextBox10.Text = ""

ComboBox7.Text = ""
ComboBox8.Text = ""
Me.TextBox4.Text = ""
Me.TextBox5.Text = ""
Me.ComboBox17.Text = ""
Me.TextBox11.Text = ""

Me.ComboBox9.Text = ""
Me.TextBox7.Text = ""
Me.TextBox13.Text = ""

Me.ComboBox10.Text = ""
Me.TextBox8.Text = ""
Me.TextBox14.Text = ""

Me.ComboBox11.Text = ""
Me.TextBox12.Text = ""
Me.TextBox15.Text = ""
End Sub

Private Sub Label14_Click()

Sheets("Due").Select

LastRow = Sheets("Due").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:H" & LastRow).Sort key1:=Range("B2:B" & LastRow), _
order1:=xlAscending, Header:=xlNo

Call Ripopola

End Sub

Private Sub Label15_Click()

Sheets("Due").Select

LastRow = Sheets("Due").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:H" & LastRow).Sort key1:=Range("C2:C" & LastRow), _
order1:=xlAscending, Header:=xlNo

Call Ripopola

End Sub

Private Sub Label16_Click()

Sheets("Due").Select

LastRow = Sheets("Due").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:H" & LastRow).Sort key1:=Range("D2:D" & LastRow), _
order1:=xlAscending, Header:=xlNo

Call Ripopola

End Sub

Private Sub Label17_Click()

Sheets("Due").Select

LastRow = Sheets("Due").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:i" & LastRow).Sort key1:=Range("E2:E" & LastRow), _
order1:=xlAscending, Header:=xlNo

Call Ripopola

End Sub

Private Sub Label18_Click()

Sheets("Due").Select

LastRow = Sheets("Due").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:I" & LastRow).Sort key1:=Range("F2:F" & LastRow), _
order1:=xlAscending, Header:=xlNo

Call Ripopola

End Sub

Private Sub Label19_Click()

Sheets("Due").Select

LastRow = Sheets("Due").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:I" & LastRow).Sort key1:=Range("G2:G" & LastRow), _
order1:=xlAscending, Header:=xlNo

Call Ripopola

End Sub

Private Sub Label20_Click()

Sheets("Due").Select

LastRow = Sheets("Due").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:I" & LastRow).Sort key1:=Range("H2:H" & LastRow), _
order1:=xlAscending, Header:=xlNo

Call Ripopola

End Sub

Private Sub Label38_Click()

Sheets("Filed").Select

LastRow = Sheets("Filed").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:I" & LastRow).Sort key1:=Range("B2:B" & LastRow), _
order1:=xlAscending, Header:=xlNo

Call PopolaCompl

End Sub

Private Sub Label39_Click()

Sheets("Filed").Select

LastRow = Sheets("Filed").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:I" & LastRow).Sort key1:=Range("C2:C" & LastRow), _
order1:=xlAscending, Header:=xlNo

Call PopolaCompl

End Sub

Private Sub Label40_Click()

Sheets("Filed").Select

LastRow = Sheets("Filed").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:I" & LastRow).Sort key1:=Range("D2:D" & LastRow), _
order1:=xlAscending, Header:=xlNo

Call PopolaCompl

End Sub

Private Sub Label41_Click()

Sheets("Filed").Select

LastRow = Sheets("Filed").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:I" & LastRow).Sort key1:=Range("E2:E" & LastRow), _
order1:=xlAscending, Header:=xlNo

Call PopolaCompl

End Sub

Private Sub Label42_Click()

Sheets("Filed").Select

LastRow = Sheets("Filed").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:I" & LastRow).Sort key1:=Range("F2:F" & LastRow), _
order1:=xlAscending, Header:=xlNo

Call PopolaCompl

End Sub

Private Sub Label43_Click()

Sheets("Filed").Select

LastRow = Sheets("Filed").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:I" & LastRow).Sort key1:=Range("G2:G" & LastRow), _
order1:=xlAscending, Header:=xlNo

Call PopolaCompl

End Sub

Private Sub Label44_Click()

Sheets("Filed").Select

LastRow = Sheets("Filed").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:I" & LastRow).Sort key1:=Range("H2:H" & LastRow), _
order1:=xlAscending, Header:=xlNo

Call PopolaCompl

End Sub

Private Sub Label45_Click()

Sheets("Filed").Select

LastRow = Sheets("Filed").Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:I" & LastRow).Sort key1:=Range("I2:I" & LastRow), _
order1:=xlAscending, Header:=xlNo

Call PopolaCompl

End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Sheets("Timesheet").Select

'Loop through every item in the ListBox
For i = 1 To ListBox1.ListCount - 1

'Check if the item was selected.
If ListBox1.Selected(i) Then

strCol0 = ListBox1.List(i, 0)
strCol1 = ListBox1.List(i, 1)
strCol2 = ListBox1.List(i, 2)
strCol3 = ListBox1.List(i, 3)
strCol4 = ListBox1.List(i, 4)
strCol5 = ListBox1.List(i, 5)
strCol6 = ListBox1.List(i, 6)
strCol7 = ListBox1.List(i, 7)

On Error Resume Next

Dim SearchRange As Range
Dim FindRow As Range
Set SearchRange = Range("A1", Range("A65536").End(xlUp))

Set FindRow = SearchRange.Find(strCol0, LookIn:=xlValues, lookat:=xlWhole)

lRow = FindRow.Row

Edit.Show
GoTo Fineciclo
End If

Next i

Fineciclo:

End Sub


Private Sub TextBox_Minuti_Change()

Call UpdateSumTS

End Sub

Private Sub TextBox13_Change()

Call UpdateSumTS

End Sub

Private Sub TextBox14_Change()

Call UpdateSumTS

End Sub

Private Sub TextBox15_Change()

Call UpdateSumTS

End Sub

Private Sub TextBox2_Change()

Call UpdateSumTS

End Sub

Private Sub TextBox5_Change()

Call UpdateSumTS

End Sub

Private Sub UserForm_Initialize()

ComboBox5.Enabled = False
ComboBox7.Enabled = False
ComboBox10.Enabled = False
ComboBox11.Enabled = False

Me.MultiPage1.Value = 0

Me.Combo_Utente.List = UserList.Value
Me.Combo_Cliente.List = CoList.Value
Me.ComboBox1.List = CoList.Value
Me.ComboBox2.List = TaskList.Value
Me.ComboBox3.List = UserList.Value
Me.ComboBox12.List = CoList.Value
Me.ComboBox13.List = TaskList.Value
Me.ComboBox14.List = UserList.Value

Me.Combo_Attività.List = ActList.Value
Me.ComboBox5.List = ActList.Value
Me.ComboBox7.List = ActList.Value

Me.ComboBox9.List = ExpList.Value
Me.ComboBox10.List = ExpList.Value
Me.ComboBox11.List = ExpList.Value

Me.ComboBox15.List = RateList.Value
Me.ComboBox16.List = RateList.Value
Me.ComboBox17.List = RateList.Value

Combo_Utente.ListIndex = Sheets("Workings").Cells(2, 7).Value - 1
Me.TextBox_Data = Date

TextBox_Minuti = 0
TextBox2 = 0
TextBox5 = 0

Sheets("Due").Select

Call PopolaCompl
Call Ripopola

End Sub


Module 3

Option Explicit

Public lRow As Integer
Public MRow As Integer
Public Juris As String
Public Compa As String

Public strCol0 As String
Public strCol1 As String
Public strCol2 As String
Public strCol3 As String
Public strCol4 As String
Public strCol5 As String
Public strCol6 As String
Public strCol7 As String

Public ValCol0 As String
Public ValCol1 As String
Public ValCol2 As String
Public ValCol3 As Variant
Public ValCol4 As String
Public ValCol5 As String
Public ValCol6 As String
Public ValCol7 As Variant
Public ValCol8 As Integer
Public ValCol9 As Integer

Public FValCol0 As Integer
Public FValCol1 As Integer
Public FValCol2 As Integer
Public FValCol3 As Integer
Public FValCol4 As Integer
Public FValCol5 As Integer
Public FValCol6 As Integer
Public FValCol7 As Integer
Public FValCol8 As Integer
Public FValCol9 As Integer
Public FValCol10 As Integer

Public CoList As Range
Public TaskList As Range
Public UserList As Range
Public JurList As Range
Public FreqList As Range

Public ActList As Range
Public AccList As Range
Public AdminList As Range
Public ConsList As Range
Public ExpList As Range
Public RateList As Range
Public PaymList As Range


Module4

Sub Ripopola()

Dim rng As Range
Dim Cel1 As Range
Dim LR As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Due")

With ws
LR = .Cells(.Rows.Count, "B").End(xlUp).Row
If LR = 1 Then GoTo Finished Else
Set rng = .Range("A1:A" & LR).SpecialCells(xlCellTypeVisible)
End With

With Scadenziario.ListBox1
.Clear
.ColumnCount = 8
.ColumnWidths = "0;180;120;60;60;60;60;270;"

For Each Cel1 In rng
.AddItem CStr(Cel1.Value)
.List(.ListCount - 1, 0) = Cel1.Offset(0, 0).Value
.List(.ListCount - 1, 1) = Cel1.Offset(0, 1).Value
.List(.ListCount - 1, 2) = Cel1.Offset(0, 2).Value
.List(.ListCount - 1, 3) = Cel1.Offset(0, 3).Value
.List(.ListCount - 1, 4) = Cel1.Offset(0, 4).Value
.List(.ListCount - 1, 5) = Cel1.Offset(0, 5).Value
.List(.ListCount - 1, 6) = Cel1.Offset(0, 6).Value
.List(.ListCount - 1, 7) = Cel1.Offset(0, 7).Value
Next Cel1
End With

Finished:

Sheets("SCFI").Select

End Sub

Module 5

Sub Ripopola()

Dim rng As Range
Dim Cel1 As Range
Dim LR As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Due")

With ws
LR = .Cells(.Rows.Count, "B").End(xlUp).Row
If LR = 1 Then GoTo Finished Else
Set rng = .Range("A1:A" & LR).SpecialCells(xlCellTypeVisible)
End With

With Scadenziario.ListBox1
.Clear
.ColumnCount = 8
.ColumnWidths = "0;180;120;60;60;60;60;270;"

For Each Cel1 In rng
.AddItem CStr(Cel1.Value)
.List(.ListCount - 1, 0) = Cel1.Offset(0, 0).Value
.List(.ListCount - 1, 1) = Cel1.Offset(0, 1).Value
.List(.ListCount - 1, 2) = Cel1.Offset(0, 2).Value
.List(.ListCount - 1, 3) = Cel1.Offset(0, 3).Value
.List(.ListCount - 1, 4) = Cel1.Offset(0, 4).Value
.List(.ListCount - 1, 5) = Cel1.Offset(0, 5).Value
.List(.ListCount - 1, 6) = Cel1.Offset(0, 6).Value
.List(.ListCount - 1, 7) = Cel1.Offset(0, 7).Value
Next Cel1
End With

Finished:

Sheets("SCFI").Select

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Loop and update from different workbooks
Replies
0
Views
253
twckfa16
T
I
  • Question
Userform does not show leading 0 for telephone number
Replies
1
Views
42
ipbr21054
I
M
  • Solved
Display textbox from listbox - getting 381 error
Replies
4
Views
197
maiatanaka
M
I
  • Question
Listbox multiple column advice
2
Replies
10
Views
299
ipbr21054
I
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
309
pure vito
P

Forum statistics

Threads
1,214,601
Messages
6,120,465
Members
448,965
Latest member
grijken

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top