Button close in userform excel 2013 requires double click

[matrixband12]

Hello
This is my first VBA project... So far I've learned plenty
The form works ok.. i set two variables as global i believe (in another module)
My current problem is that the cancel button requires 2 clicks and the screen flickers. The other buttons all work with a single click. This is not a fatal problem...but i dont understand why it does NOT hide the form on the first click... I even tried to change the focus to the cancel button after each sub... and it still requires two clicks.... they can to spaced apart.... In other words... I can hit cancel...then "next" then submit and if i hit cancel it will hide the form

Here is the code:



Private Sub UserForm_Initialize()


'Call LoadWeek
'MsgBox (ActiveCell.Value)


Call TBRecord_load
'Call trimWeekInsert

End Sub






Private Sub CommandButton1_Click()
Dim sdsheet1 As Worksheet
Dim CurrRow As Integer




CurrRow = 2




'Set shsheet1 = Worksheet.Sheets("sheet1")


'CurrRow = Application.WorksheetFunction.Match(Me.lblName, Sheets("sheet1").Range("e1:e8"), 0)
'CurrRow = sheet1.Cells(X, 1)


Set sdsheet1 = ThisWorkbook.Sheets("Sheet1")


If UserFormMod.TBAdj.Value = "" Then
UserFormMod.TBAdj.Value = 0
Else
sdsheet1.Cells(CurrRecord, 9) = UserFormMod.TBAdj.Value + 0
End If


If UserFormMod.TBVac.Value = "" Then
UserFormMod.TBVac.Value = 0
Else
sdsheet1.Cells(CurrRecord, 10) = UserFormMod.TBVac.Value + 0
End If

If UserFormMod.TBHoliday.Value = "" Then
UserFormMod.TBHoliday.Value = 0
Else
sdsheet1.Cells(CurrRecord, 12) = UserFormMod.TBHoliday.Value + 0
End If


If UserFormMod.TBPerSick.Value = "" Then
UserFormMod.TBPerSick.Value = 0
Else
sdsheet1.Cells(CurrRecord, 11) = UserFormMod.TBPerSick.Value + 0
End If

If UserFormMod.TBWeather.Value = "" Then
UserFormMod.TBWeather.Value = 0
Else
sdsheet1.Cells(CurrRecord, 13) = UserFormMod.TBWeather.Value + 0
End If




















UserFormMod.TBAdj.Value = ""


UserFormMod.TBVac.Value = ""
UserFormMod.TBPerSick.Value = ""
UserFormMod.TBHoliday.Value = ""
UserFormMod.TBWeather.Value = ""
'Me.lblTotal = Application.WorksheetFunction.VLookup(Me.lblName, [TableformEntry], 9, 0)
Me.TBAdj.SetFocus


End Sub














Sub TBRecord_load()
Dim LRrow As Integer


Dim X As Integer


Dim sheet1 As Worksheet


Dim TBRecord As Double
'Dim DnTablEmp As Range


'Dim CntEmp As Range


'Dim TrimAmt As Integer
'Dim FstEmp As Range


Set sheet1 = ThisWorkbook.Sheets("Sheet1")






UserFormMod.TBRecord.Value = ""








'If sdsheet1.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
' LRrow = 3
'Else
' LRrow = sdsheet1.Cells(Rows.Count, 1).End(xlUp).Row
'End If
'For X = 3 To LRrow
X = 3
CurrRecord = ThisWorkbook.Sheets("sheet1").Cells(X, 1).Row

UserFormMod.TBRecord.Value = sheet1.Cells(X, 1)
UserFormMod.TBWeekNum.Value = Cells(X, 2).Value + 0
'MsgBox X

UserFormMod.TBRecord.Value = Cells(X, 1).Value + 0
UserFormMod.TBWeekStart.Value = Cells(X, 4).Value + 0
UserFormMod.TBEmpName.Value = Cells(X, 6).Value

UserFormMod.TBTCHours.Value = Cells(X, 8).Value + 0
UserFormMod.TBAdj.Value = Cells(X, 9).Value + 0

UserFormMod.TBVac.Value = Cells(X, 10).Value + 0
UserFormMod.TBPerSick.Value = Cells(X, 11).Value + 0
UserFormMod.TBHoliday.Value = Cells(X, 12).Value + 0
UserFormMod.TBWeather.Value = Cells(X, 13).Value + 0
UserFormMod.TBTotal.Value = Cells(X, 14).Value + 0

'MsgBox X

' UserFormMod.TBVac.Value = ""
'UserFormMod.TBPerSick.Value = ""
'UserFormMod.TBHoliday.Value = ""
'UserFormMod.TBWeather.Value

'Load UserFormMod
UserFormMod.Show

'Call cmdNext_Click
'Next X


Me.TBAdj.SetFocus




End Sub


Private Sub cmdNext_Click()


X = CurrRecord
'MsgBox ("row is" & X & "before the x=x+1")

X = X + 1




CurrRecord = ThisWorkbook.Sheets("sheet1").Cells(X, 1).Row

UserFormMod.TBRecord.Value = sheet1.Cells(X, 1)
UserFormMod.TBWeekNum.Value = Cells(X, 2).Value + 0
'MsgBox X

'UserFormMod.TBRecord.Value = Cells(X, 1).Value + 0
UserFormMod.TBWeekStart.Value = Cells(X, 4).Value
UserFormMod.TBEmpName.Value = Cells(X, 6).Value
UserFormMod.TBTCHours.Value = Cells(X, 8).Value + 0
UserFormMod.TBAdj.Value = Cells(X, 9).Value + 0

UserFormMod.TBVac.Value = Cells(X, 10).Value
UserFormMod.TBPerSick.Value = Cells(X, 11).Value
UserFormMod.TBHoliday.Value = Cells(X, 12).Value
UserFormMod.TBWeather.Value = Cells(X, 13).Value
UserFormMod.TBTotal.Value = Cells(X, 14).Value

'UserFormMod.Hide
'UserFormMod.Show




Me.TBAdj.SetFocus




End Sub
Sub cmdClose_Click()








UserFormMod.Hide








End Sub




Private Sub cmdPrevious_Click()


X = CurrRecord
'MsgBox ("row is" & X & "before the x=x+1")

X = X - 1




CurrRecord = ThisWorkbook.Sheets("sheet1").Cells(X, 1).Row

UserFormMod.TBRecord.Value = sheet1.Cells(X, 1)
UserFormMod.TBWeekNum.Value = Cells(X, 2).Value + 0
'MsgBox X

'UserFormMod.TBRecord.Value = Cells(X, 1).Value + 0
UserFormMod.TBWeekStart.Value = Cells(X, 4).Value
UserFormMod.TBEmpName.Value = Cells(X, 6).Value
UserFormMod.TBTCHours.Value = Cells(X, 8).Value + 0
UserFormMod.TBAdj.Value = Cells(X, 9).Value + 0

UserFormMod.TBVac.Value = Cells(X, 10).Value
UserFormMod.TBPerSick.Value = Cells(X, 11).Value
UserFormMod.TBHoliday.Value = Cells(X, 12).Value
UserFormMod.TBWeather.Value = Cells(X, 13).Value
UserFormMod.TBTotal.Value = Cells(X, 14).Value

'UserFormMod.Hide
'UserFormMod.Show




Me.TBAdj.SetFocus


End Sub



Thank you for looking at the code.

I am surely open to other ideas about how to make the userform in general....


Tom

 

[matrixband12]

here is the code for that button
Sub cmdClose_Click()

UserFormMod.Hide


End Sub