VBA Project - Keeping the Userform On top of all active windows

[Twizle]

Hi all,

I am trying to find a solution to keep the User form window on top of all other active windows on the PC.
I have tried several solutions but I am a bit of a novice when it comes to coding so I am struggling.
Below is what I have so far:

Sheet 1 Code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Integer
For X = 2 To 1000
If Cells(X, 1).Value <> "" And Cells(X, 2).Value = "" Then
Cells(X, 2).Value = Date & " " & Time
Cells(X, 2).NumberFormat = "d/m/yyyy h:mm:ss AM/PM"
End If
Next
Range("B:B").EntireColumn.AutoFit
Application.EnableEvents = False
If Target.Address = "$A$2" Then
Range("B2").Value = Date & " " & Time()
Range("A3").Select
End If
Application.EnableEvents = True
End Sub

Private Sub CommandButton1_Click()
Application.WindowState = xlMinimized
Unload UserForm1
UserForm1.Show Modeless
End Sub



UserForm1 code

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then
Dim emptyRow As Long
emptyRow = WorksheetFunction.CountA(Range("A2:A" & Rows.Count)) + 2
Cells(emptyRow, 1).Value = TextBox1.Value
TextBox1.Value = ""
TextBox1.SetFocus
End If
End Sub

Please help?

 

[JohnnySix]

There's only really the option of displaying the form as modal or not, with modal it forces the form to be closed before allowing the user back to excel, but as far as I know, if you displaying non-modal like you have ( with the UserForm1.Show Modeless ) then if the form loses focus, it can be pushed behind a window.

You could add calls to the actual windows api to force it to the top of the stack upon initialising, but that might be a bit iffy, as anything making calls to windows API directly can be, as you have no idea about version etc.

If you can , always go for the simpler ( if less technically astounding ) option - less to break. :D