Userform - startup position

[Lukael]

Hello,

I have Userform to display entered data in cells, and needs to be navigated through sheet while entering data. I want Userform to stay at It's last position when I hit "Refresh" button.

Tried all positions in Startup Property but none works for me, so far best option is to position It to center.

my "Refresh" button code :

Private Sub CommandButton2_Click()

Unload Me
UserForm1.Show vbModeless

End Sub

Any ideas ?

Thanks in advance !

[Tinbendr]

Private Sub Userform1_Intialize()
with Userform1
    .Top = 50
    .Left = 100
End With
End Sub

[Lukael]

Hi Tinbendr,

Your code probably just sets Userform in center, no need for that as I allready have set that in Property. Although I tried, still the same as before.

Explanation, again : If I open Userform and change It's position... Then I want this "last" position "remembered" when I hit refresh button, which opens Userform again. I hate when It centers again, kind of annoying when entering data in cells.

[Leith Ross]

Hello Lukae,

I have some code that will make a UserForm function like a window. Would that work for you ?

[Lukael]

I don't know what to say. All I want is that Userform wouldn't move when I close and re-open It. If this does that, than YES OFCOURSE

[Leith Ross]

Hello Lukael,

I have had a difficult time staying connected to the forum. Here is the code I promised. Yes, adding this will remember where the UserForm was positioned last while it is open. The default start position is the center of the screen.

Copy the code below and paste it into a new VBA module in your workbook. Change you UserForm to non-modal by changing the ShowModal property to False.

Call the macro from the UserForm's Activate event.

Private Sub UserForm_Activate()
        ConvertToWindow
End Sub

Macro Code To Convert UserForm to Window

' Written: March 12, 2014
' Author:  Leith Ross
' Summary: Adds Window functionality to a UserForm.

' Notes:    1) The UserForm must be non-modal.
'           2) Place a call to ConvertToWindow in  the UserForm's Activate event.
            
Private Const GWL_STYLE As Long = -16
Private Const MIN_BOX   As Long = &H20000
Private Const MAX_BOX   As Long = &H10000

Const SC_CLOSE      As Long = &HF060
Const SC_MAXIMIZE   As Long = &HF030
Const SC_MINIMIZE   As Long = &HF020
Const SC_RESTORE    As Long = &HF120

Private Declare Function SetWindowPos Lib "user32.dll" (ByVal hWnd As Long, ByVal hWndInsertAfter As Long, ByVal X As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long

Private Declare Function GetWindowLong Lib "user32.dll" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
               
Private Declare Function SetWindowLong Lib "user32.dll" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
     
'Redraw the Icons on the Window's Title Bar
Private Declare Function DrawMenuBar Lib "user32.dll" (ByVal hWnd As Long) As Long

'Returns the Window Handle of the Window accepting input
Private Declare Function GetForegroundWindow Lib "user32.dll" () As Long

Public Sub ConvertToWindow()

    Dim BitMask         As Long
    Dim hWnd            As Long
    Dim WindowStyle     As Long
    Dim Ret             As Long

        hWnd = GetForegroundWindow()
  
        WindowStyle = GetWindowLong(hWnd, GWL_STYLE)
        BitMask = WindowStyle Or MIN_BOX Or MAX_BOX
  
        Ret = SetWindowLong(hWnd, GWL_STYLE, BitMask)
        Ret = DrawMenuBar(hWnd)

End Sub

[Lukael]

Thanks Leith,

I pasted code below in new module, set Modal property to False, and pasted this in Userform's code :

Private Sub UserForm_Activate()
        ConvertToWindow
End Sub

....And, It's not working. What did I do wrong ?

So far I had Userform_initialize event, and some code beneath It, so I put that code in _activate event, but nothing happens. It tried with both also

[Leith Ross]

Hello Lukael,

Can you post a copy of the workbook for me to review?

[Lukael]

Yes sure, here It is

[Leith Ross]

Hello Lukael,


The macro "ConvertToWindow" was not saved in the workbook. I have added the module and tested the workbook. Everything works as it should.

[mc84excel]

Hello,

I have Userform to display entered data in cells, and needs to be navigated through sheet while entering data. I want Userform to stay at It's last position when I hit "Refresh" button.

Tried all positions in Startup Property but none works for me, so far best option is to position It to center.

my "Refresh" button code :

Private Sub CommandButton2_Click()

Unload Me
UserForm1.Show vbModeless

End Sub

Any ideas ?

Thanks in advance !

This...

(Explanation: 1. Because you don't know where the user will drag the window too, it is impossible to hard code the positions in the Initialize event. 2. Because you are refreshing your form by Unloading then reloading, all form variables are scrapped so Initialize can't rely on the use of module level variables for the last form positon. However if you have project/global level variables in a standard module then these are not cleared by the form unloading and therefore they can be read by Initialize event when you 'refresh' your form. Easy. )

[Lukael]

I'm sorry Leith & mc84excel,

guess like you two didn't fully understand me. I don't even know what your codes do, too complicated for me.

I tried both solutions, but everything is same as before. So, I will explain again differently :

This sheet is a monthly schedule for working hours of employees. I assigned one button for Userform which displays all data about entered data for each day and each employee.

I move opened Userform left/right or up/down (depends on a date and row of employee), because I want to enter data in cells and see the Userfom, both together - this is done by VbModeless command.

And after a while I want to update Userform with mentioned "refresh" button, so that I can see what data were allready entered. And at that point I want Userform to stay opened where It was before clicking "refresh" button.

Maybe solutions works for you two, but for me - when I move Userform's position after opening It & then click refresh button, It moves back to center of the screen, as in beginning.

I have Exel 2003 although, maybe that is problem.

[Lukael]

Hi again,

I tried both codes again on my home PC and mc84excel's code worked ! Not sure why It didn't on my job PC as I have same Excel 2003 version. Maybe some add-ins missing ?

Code is exactly what I wanted !!! ....I'm still figuring out how It actually works, despite your explanation everything is still not quite clear to me

Too bad that I can't check Leith's solution, must be great code too but not sure why nothing happens

Anyway, mc84excel, thanks for your code, and If this is your masterpiece I must say - You're a genious

[mc84excel]

Anyway, mc84excel, thanks for your code, and If this is your masterpiece I must say - You're a genious

Glad to help

If you wish to thank me, you could consider taking a few seconds to rep my post. (Click on the star in the bottom left hand corner of the post).

If your problem is now solved, please use the Thread Tools (top right hand corner of the page) to mark the thread as SOLVED.

mc84excel's code worked ! Not sure why It didn't on my job PC as I have same Excel 2003 version. Maybe some add-ins missing ?

My code doesn't require any add-ins. Did you run the downloaded workbook on the job PC or was it after you copied the code into your original workbook?

I'm still figuring out how It actually works, despite your explanation everything is still not quite clear to me

I'm happy to explain it in more detail if you want.

[Lukael]

My code doesn't require any add-ins. Did you run the downloaded workbook on the job PC or was it after you copied the code into your original workbook?

No, in both cases I downloaded file from this forum and tried. On job It didn't work, but on my home PC worked so I pasted code in my original workbook. I will try again on work to see If there is any changes.

I'm happy to explain it in more detail if you want.

Yes, I would very like that, I save all my files and try to comment codes so that I learn from It

If you wish to thank me, you could consider taking a few seconds to rep my post. (Click on the star in the bottom left hand corner of the post).

DONE !

[mc84excel]

Yes, I would very like that, I save all my files and try to comment codes so that I learn from It

Quick notes below:
You wanted a user form to keep its position that the end user last dragged it to before hitting Refresh.
Your refresh code works by Unloading the form and then loading the form again. Doing this causes the Initialize event to fire.
When a form initializes - the forms position is set using the Top & Left properties (if these are not set, VBA uses a default position)
However you can also run-time set the forms position in the UserForm_Initialize event (by setting Me.Top and Me.Left).

So far so good.

So all we need to do is find a way to write the Top & Left positions after the user drags the form and then read these values back at UserForm_Initialize

A quick google search revealed that the UserForm_Layout event would cover the user dragging the form. This is the point where you would want to call some code to write the forms current Top & Left positions.

The question then is how to store these values? There are multiple ways we could do this. But the most simplest way would be to use variables.

For the purpose of this exercise, they can not be stored in the form (the values of form level variables are lost when a form unloads) so I added two global level variables to a standard module instead.

When the user drags the form, the UserForm_Layout event fires and updates the two global variables.

When you open/refresh the form ( UserForm_Initialize ), it checks to see if these global variables are set. If they are then set the forms Top & Left to these values. If they are not set (first time form opened for this session), then write the opening values to the variables.

[Lukael]

I tried again on both PC's with same file. At home It works, at job not working. What's wrong, Excel version is same, the only difference I see is that I have Win 7 at home, and XP on work. Is that the case maybe ?

[mc84excel]

I tried again on both PC's with same file. At home It works, at job not working.

Hi Lukael,

Sorry to hear it is not working on both machines.

What's wrong, Excel version is same, the only difference I see is that I have Win 7 at home, and XP on work. Is that the case maybe ?

The code relies on two global level variables. ( lngUFpositionTop & lngUFpositionLeft). It works by reading/updating the variable values on the form Initialize event.
Variables are not dependent on the version of Windows OS or Office so I am puzzled as to why it is not working for you at your job.

At home It works, at job not working.

When you say not working - do you mean that the form doesn't keep the same position after Refresh? Or will the form not even open?

[Lukael]

Oh, and every settings are exact same, even add-ins...

[Izandol]

Why must you close and reopen form? It is better to have routine to reset controls.

[Lukael]

Why must you close and reopen form? It is better to have routine to reset controls.

Hi Izandol, could you give me an example, maybe a sample worksheet ?

[Lukael]

The code relies on two global level variables. ( lngUFpositionTop & lngUFpositionLeft). It works by reading/updating the variable values on the form Initialize event.
Variables are not dependent on the version of Windows OS or Office so I am puzzled as to why it is not working for you at your job.

Thanks for explanation of code, but I think that I was not wrong. I tested code on 3 different PC's with Win 7 and 4 different PC's with Win XP, with same file. Result was : Win 7 worked, Win XP didn't work.
That was tested on Excel 2003 and Excel 2007, no PC is connected via network, just separate machines on separate locations.

Win XP PC's were all SP3, guess like something is wrong there, maybe because of near ending to updates for that OS.

[Izandol]

With your example workbook, code will be like:

Private Sub CboDatum_Change()

'definiramo spremenljivke

   Dim Index                       As Long
   Dim lngRes                      As Long
   Dim lngArr                      As Long
   Dim varItem                     As Variant

   Const ShiftDay                  As String = "0"   ' character beginning day shifts
   Const ShiftNight                As String = "1"   ' character beginning night shifts

   Const JobOP1                    As String = "OP1"
   Const JobOP2                    As String = "OP2"
   Const JobDEZ                    As String = "DEŽ"
   Const JobPDZ                    As String = "PDZ"

   ' Counts for each combination of job and shift
   Dim Counts(1 To 2, 1 To 4)      As Long

   Dim DateCol                     As Long
   Dim HeaderRow                   As Long   ' contains the column header for each name

   ' only run if item selected
   If CboDatum.ListIndex = -1 Then Exit Sub

   DateCol = CboDatum.ListIndex + 5   ' column with date selected from combobox


   HeaderRow = 3   ' row of headers for first name

   With ThisWorkbook.ActiveSheet
      For Index = 0 To Lstuporabnik.ListCount - 1

         ' Populate list box according the date selected in the combobox
         Lstuporabnik.List(Index, 1) = .Cells(HeaderRow, DateCol)
         Lstuporabnik.List(Index, 2) = .Cells(HeaderRow + 1, DateCol)

         ' Accumulate counts for each combination of job and shift
         ' First character of shift indicates whether day or night
         Select Case Mid(.Cells(HeaderRow + 1, DateCol), 1, 1)

            Case ShiftDay
               Select Case .Cells(HeaderRow, DateCol)
                  Case JobOP1
                     Counts(1, 1) = Counts(1, 1) + 1
                  Case JobOP2
                     Counts(1, 2) = Counts(1, 2) + 1
                  Case JobDEZ
                     Counts(1, 3) = Counts(1, 3) + 1
                  Case JobPDZ
                     Counts(1, 4) = Counts(1, 4) + 1
               End Select

            Case ShiftNight
               Select Case .Cells(HeaderRow, DateCol)
                  Case JobOP1
                     Counts(2, 1) = Counts(2, 1) + 1
                  Case JobOP2
                     Counts(2, 2) = Counts(2, 2) + 1
                  Case JobDEZ
                     Counts(2, 3) = Counts(2, 3) + 1
                  Case JobPDZ
                     Counts(2, 4) = Counts(2, 4) + 1
               End Select

         End Select

         ' Each name uses 4 rows, go to next name
         HeaderRow = HeaderRow + 4

      Next Index
   End With

   ' Tick checkboxes appropriately.
   ' Show message boxes if more than one assignment to the same job/shift combination

   CheckBoxOP1Day = Counts(1, 1) = 1
   If Counts(1, 1) > 1 Then
      MsgBox Counts(1, 1) & " assignments to " & JobOP1 & " Day Shift"
   End If
   CheckBoxOP1Night = Counts(2, 1) = 1
   If Counts(2, 1) > 1 Then
      MsgBox Counts(2, 1) & " assignments to " & JobOP1 & " Night Shift"
   End If

   CheckBoxOP2Day = Counts(1, 2) = 1
   If Counts(1, 2) > 1 Then
      MsgBox Counts(1, 2) & " assignments to " & JobOP2 & " Day Shift"
   End If
   CheckBoxOP2Night = Counts(2, 2) = 1
   If Counts(2, 2) > 1 Then
      MsgBox Counts(2, 2) & " assignments to " & JobOP2 & " Night Shift"
   End If

   CheckBoxDEZDay = Counts(1, 3) = 1
   If Counts(1, 3) > 1 Then
      MsgBox Counts(1, 3) & " assignments to " & JobDEZ & " Day Shift"
   End If
   CheckBoxDEZNight = Counts(2, 3) = 1
   If Counts(2, 3) > 1 Then
      MsgBox Counts(2, 3) & " assignments to " & JobDEZ & " Night Shift"
   End If

   CheckBoxPDZDay = Counts(1, 4) = 1
   If Counts(1, 4) > 1 Then
      MsgBox Counts(1, 4) & " assignments to " & JobPDZ & " Day Shift"
   End If
   CheckBoxPDZNight = Counts(2, 4) = 1
   If Counts(2, 4) > 1 Then
      MsgBox Counts(2, 4) & " assignments to " & JobPDZ & " Night Shift"
   End If

End Sub

Private Sub CommandButtonClose_Click()
   Unload Me
End Sub

Private Sub CommandButtonRefresh_Click()
   Call resetControls
   Call setup
End Sub

Private Sub UserForm_Initialize()
   Call setup
End Sub

Sub setup()
   Dim lngDay                      As Long
   Dim lngRow                      As Long
   Dim rng                         As Range

   CboDatum.Clear
   With ActiveWorkbook.ActiveSheet

      Set rng = .Range("E2")
      ' Load combobox with dates from worksheet
      Do
         Me.CboDatum.AddItem Format(rng.Value, "d/ (ddd)")
         Set rng = rng.Offset(, 1)
      Loop Until rng.Value = "Prenos"

      ' Load listbox with names from worksheet
      For lngRow = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row Step 4
         Me.Lstuporabnik.AddItem .Cells(lngRow, 2)
      Next

   End With

   CboDatum.ListIndex = 0

   ' Set caption to month of active worksheet
   Me.LblDatum.Caption = Format(Range("E2").Value, "MMMM YYYY")

End Sub

Sub resetControls()
   Dim ctl                         As MSForms.Control
   For Each ctl In Me.Controls
      Select Case LCase$(TypeName(ctl))
         Case "checkbox", "optionbutton", "togglebutton"
            ctl.Value = False
         Case "combobox", "listbox"
            ctl.ListIndex = -1
         Case "textbox"
            ctl.Text = vbNullString
      End Select
   Next ctl
End Sub

[Lukael]

Hi Izandol,
I tried on sample and It works but I don't know how to do the code in my workbook as I have more code than in sample provided. Could you take a look at my code and try to do It again ?

Option Explicit

Private Sub CboDatum_Change()

   Dim Index As Long
   Dim lngRes As Long
   Dim lngArr As Long
   Dim varItem As Variant
   
   Const Dnevna As String = "0" ' začeten znak dnevne službe
   Const Nocna As String = "1" ' začeten znak nočne službe
   Const Nocna2 As String = "2" ' drugi začeten znak nočne službe
   
   Const OP1 As String = "OP1"
   Const OP2 As String = "OP2"
   Const DEZ As String = "DEŽ"
   Const PDZ As String = "PDZ"
   Const MKIU As String = "MKIU"
   Const MKP As String = "MKP"
   
   Dim Stetje(1 To 2, 1 To 6) As Long
   
   Dim DateCol As Long
   Dim GlavaIme As Long 
   
   DateCol = CboDatum.ListIndex + 5 
      
   GlavaIme = 3 
   
   With ThisWorkbook.ActiveSheet
      For Index = 0 To LstUporabnik.ListCount - 1
      
                 
         LstUporabnik.List(Index, 1) = .Cells(GlavaIme, DateCol)
         LstUporabnik.List(Index, 2) = .Cells(GlavaIme + 1, DateCol)
         
         
         Select Case Mid(.Cells(GlavaIme + 1, DateCol), 1, 1)
         
            Case Dnevna
               Select Case .Cells(GlavaIme, DateCol)
                  Case OP1
                     Stetje(1, 1) = Stetje(1, 1) + 1
                  Case OP2
                     Stetje(1, 2) = Stetje(1, 2) + 1
                  Case DEZ
                     Stetje(1, 3) = Stetje(1, 3) + 1
                  Case PDZ
                     Stetje(1, 4) = Stetje(1, 4) + 1
                  Case MKIU
                     Stetje(1, 5) = Stetje(1, 5) + 1
                  Case MKP
                     Stetje(1, 6) = Stetje(1, 6) + 1
                  
               End Select
            
            Case Nocna, Nocna2
               Select Case .Cells(GlavaIme, DateCol)
                  Case OP1
                     Stetje(2, 1) = Stetje(2, 1) + 1
                  Case OP2
                     Stetje(2, 2) = Stetje(2, 2) + 1
                  Case DEZ
                     Stetje(2, 3) = Stetje(2, 3) + 1
                  Case PDZ
                     Stetje(2, 4) = Stetje(2, 4) + 1
                  Case MKIU
                     Stetje(2, 5) = Stetje(2, 5) + 1
                     Case MKP
                     Stetje(2, 6) = Stetje(2, 6) + 1
               End Select
               
         End Select
         
            
         GlavaIme = GlavaIme + 4
         
      Next Index
  
      
   CheckBoxOP1Dnevna = Stetje(1, 1) = 1
   If Stetje(1, 1) > 1 Then
      MsgBox Stetje(1, 1) & " x vnos za " & OP1 & " - dnevna !", , "Opozorilo"
   End If
   CheckBoxOP1Nocna = Stetje(2, 1) = 1
   If Stetje(2, 1) > 1 Then
      MsgBox Stetje(2, 1) & " x vnos za " & OP1 & " - nočna !", , "Opozorilo"
   End If
   
   CheckBoxOP2Dnevna = Stetje(1, 2) = 1
   If Stetje(1, 2) > 1 Then
      MsgBox Stetje(1, 2) & " x vnos za " & OP2 & " - dnevna !", , "Opozorilo"
   End If
   CheckBoxOP2Nocna = Stetje(2, 2) = 1
   If Stetje(2, 2) > 1 Then
      MsgBox Stetje(2, 2) & " x vnos za " & OP2 & " - nočna !", , "Opozorilo"
   End If

   CheckBoxDEZDnevna = Stetje(1, 3) = 1
   If Stetje(1, 3) > 1 Then
      MsgBox Stetje(1, 3) & " x vnos za " & DEZ & " - dnevna !", , "Opozorilo"
   End If
   CheckBoxDEZNocna = Stetje(2, 3) = 1
   If Stetje(2, 3) > 1 Then
      MsgBox Stetje(2, 3) & " x vnos za " & DEZ & " - nočna !", , "Opozorilo"
   End If
   
   CheckBoxPDZDnevna = Stetje(1, 4) = 1
   If Stetje(1, 4) > 1 Then
      MsgBox Stetje(1, 4) & " x vnos za " & PDZ & " - dnevna !", , "Opozorilo"
   End If
   CheckBoxPDZNocna = Stetje(2, 4) = 1
   If Stetje(2, 4) > 1 Then
      MsgBox Stetje(2, 4) & " x vnos za " & PDZ & " - nočna !", , "Opozorilo"
   End If
   CheckBoxMKIU = (Stetje(1, 5) = 1 Or Stetje(2, 5) = 1)
    If (Stetje(1, 5) > 1 Or Stetje(2, 5) > 1 Or (Stetje(1, 5) And Stetje(2, 5))) Then
    MsgBox "Preveč vnosov za MKIU !", , "Opozorilo"
    CheckBoxMKIU = False
    End If
   
   CheckBoxMKP1 = (Stetje(1, 6) = 1 Or Stetje(1, 6) = 2 Or Stetje(2, 6) = 1 Or Stetje(2, 6) = 2)
   CheckBoxMKP2 = Stetje(1, 6) = 2 Or Stetje(2, 6) = 2 Or ((Stetje(1, 6) + Stetje(2, 6)) = 2)
   If Stetje(1, 6) > 2 Or Stetje(2, 6) > 2 Or ((Stetje(1, 6) + Stetje(2, 6)) > 2) Then
   MsgBox Stetje(1, 6) + Stetje(2, 6) & " x vnos za " & MKP & " !", , "Opozorilo"
   End If

  
      
      TextBox1.Text = "Ostale službe" & vbTab & vbNewLine & String(39, "-") & _
       vbNewLine & "1. Pom. komandirja (PK)  :" & vbTab & WorksheetFunction.CountIf(.Range(.Cells(3, DateCol), .Cells(319, DateCol)), "PK") & _
      vbNewLine & vbNewLine & "2. Vodja okoliša (VPO)     :" & vbTab & WorksheetFunction.CountIf(.Range(.Cells(3, DateCol), .Cells(319, DateCol)), "VPO") & _
      vbNewLine & vbNewLine & "3. Kriminalist  (KRIM)       :" & vbTab & WorksheetFunction.CountIf(.Range(.Cells(3, DateCol), .Cells(319, DateCol)), "KRIM") & _
      vbNewLine & vbNewLine & "4. Administracija (ADM)   :" & vbTab & WorksheetFunction.CountIf(.Range(.Cells(3, DateCol), .Cells(319, DateCol)), "ADM") & _
       vbNewLine & vbNewLine & "5. M.K - Odhodi (MKO)     :" & vbTab & WorksheetFunction.CountIf(.Range(.Cells(3, DateCol), .Cells(319, DateCol)), "MKO")
       
     End With
     
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub
  
Private Sub CommandButton2_Click()

Unload Me
UserForm1.Show vbModeless

End Sub


Private Sub UserForm_Initialize()

    Dim lngDay As Long
    Dim lngRow As Long
    Dim rng As Range
    
    With ActiveWorkbook.ActiveSheet
   
    Set rng = .Range("E2")

Do
    Me.CboDatum.AddItem Format(rng.Value, "d/ (ddd)")
    Set rng = rng.offset(, 1)
Loop Until rng.Value = "Prenos"
                 
        Me.LstUporabnik.ColumnCount = 3
        
        For lngRow = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row Step 4
        Me.LstUporabnik.AddItem .Cells(lngRow, 2)
      
       
        Next
    End With
       
    CboDatum.ListIndex = 0
    
    Me.LblDatum.Caption = Format(Range("E2").Value, "MMMM YYYY")
  
End Sub

[Izandol]

This is part you need:

Private Sub CommandButtonRefresh_Click()
   Call resetControls
   Call setup
End Sub

Private Sub UserForm_Initialize()
   Call setup
End Sub

Sub setup()
   Dim lngDay                      As Long
   Dim lngRow                      As Long
   Dim rng                         As Range

   CboDatum.Clear
   With ActiveWorkbook.ActiveSheet

      Set rng = .Range("E2")
      ' Load combobox with dates from worksheet
      Do
         Me.CboDatum.AddItem Format(rng.Value, "d/ (ddd)")
         Set rng = rng.Offset(, 1)
      Loop Until rng.Value = "Prenos"

      ' Load listbox with names from worksheet
      For lngRow = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row Step 4
         Me.Lstuporabnik.AddItem .Cells(lngRow, 2)
      Next

   End With

   CboDatum.ListIndex = 0

   ' Set caption to month of active worksheet
   Me.LblDatum.Caption = Format(Range("E2").Value, "MMMM YYYY")

End Sub

Sub resetControls()
   Dim ctl                         As MSForms.Control
   For Each ctl In Me.Controls
      Select Case LCase$(TypeName(ctl))
         Case "checkbox", "optionbutton", "togglebutton"
            ctl.Value = False
         Case "combobox", "listbox"
            ctl.ListIndex = -1
         Case "textbox"
            ctl.Text = vbNullString
      End Select
   Next ctl
End Sub

[Lukael]

Thanks, It works !!

A little bit slower but It does as needed

[Lukael]

Hi,
Izandol, It's me again.
Could you explain a little bit this part of code, and how whole code actually works ?

Sub resetControls()
   Dim ctl                         As MSForms.Control
   For Each ctl In Me.Controls
      Select Case LCase$(TypeName(ctl))
         Case "checkbox", "optionbutton", "togglebutton"
            ctl.Value = False
         Case "combobox", "listbox"
            ctl.ListIndex = -1
         Case "textbox"
            ctl.Text = vbNullString
      End Select
   Next ctl
End Sub

[Izandol]

This code will check type of each control on form and reset to default value dependent upon control type.

[Lukael]

Izandol,

I fully tested your code, but I'm afraid It wasn't working quite as It should. Code actually duplicates records, which is not what I wanted. When I pressed refresh button, It adds another whole lines of names into Listbox, which was not desired, because I have scrollbar on listbox.

So I had to first clear listbox, now It works !

Got any clues on how to set comobobox listindex to remain in same value - index after refresh ?

[Izandol]

You may store value before refresh and reset afterwards. Or do not clear combobox.

[Lukael]

Hi again,

I managed to fix my issues in another way. I just populated listbox, textbox and checkboxes on my userform, and leave combobox intacted. And It works excatly as I want

Thanks for all your help though !!