Problem writing to minimised workbook - worksheet minimised when saved

[macnab]

My user for writes to a workbook.
If I set the workbook to Visible = False, the data doesn't get written to it.
If I use Application.WindowState = xlMinimized the data doesn't get written to it.
If i use ActiveWindow.WindowState = xlMinimized, the data does get written, but when I open the workbookm the worksheet is minimised.

This is what I do when saving the workbook:


Application.DisplayAlerts = False
Application.ScreenUpdating = True
ActiveWindow.WindowState = xlNormal
FormFile.Save

I have tried setting Screenupdating to false and then true after WindowState, but it still doesn't help.
Does Excel perhaps need a bit of time to restore the worksheet before saving?

[georgiboy]

The problem could be earlier in the code, Can you provide the whole sub that contains the issue?

[macnab]

If I don't "hide" the worksheet I can see it being updated as I tab out of each control on the userform. And if I minimise the ActiveForm it gets updated. It is just minimised when I open it later.

It is a lot of code, but here it is. FormFile is the workbook that gets written to.

frmCashup

Option Explicit

Dim GettingDate As Boolean
Dim TotalPaidOut As Double
Dim CashInTill As Double
Dim Change As Double
Dim TillArray(11) As Double
Dim FloatArray(4) As Double
'************************************************************************** **
'
' Buttons
'
'************************************************************************** **
Private Sub cmdPrint_Click()


' Need to write


End Sub
'************************************************************************** **
Private Sub cmdSave_Click()


SaveThisFile

End Sub
'************************************************************************** **
Public Sub cmdDone_Click()


AllDone

End Sub
'************************************************************************** **
'
' Label & Text Box Exit routines
'
'************************************************************************** **
Private Sub txtStaff_Exit(ByVal Cancel As MSForms.ReturnBoolean)


FormFile.Sheets("Cashup").Range("Staff").Cells(1, 1).Value = Me("txtStaff").Value

End Sub
'************************************************************************** **
'
' Paid Out
'
'************************************************************************** **
Private Sub txtItem1_Exit(ByVal Cancel As MSForms.ReturnBoolean)


PaidOutItemExit (1)

End Sub
'************************************************************************** **
Private Sub txtItem2_Exit(ByVal Cancel As MSForms.ReturnBoolean)


PaidOutItemExit (2)

End Sub
Private Sub txtItem3_Exit(ByVal Cancel As MSForms.ReturnBoolean)


PaidOutItemExit (3)

End Sub
Private Sub txtItem4_Exit(ByVal Cancel As MSForms.ReturnBoolean)


PaidOutItemExit (4)

End Sub
Private Sub txtItem5_Exit(ByVal Cancel As MSForms.ReturnBoolean)


PaidOutItemExit (5)

End Sub
Sub PaidOutItemExit(TheRow As Integer)


FormFile.Sheets("Cashup").Range("PaidOut").Cells(TheRow, 1).Value = Me("txtItem" & TheRow).Value

End Sub


Private Sub txtValueItem1_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = PaidOutValueExit(1)

End Sub
Private Sub txtValueItem2_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = PaidOutValueExit(2)

End Sub
Private Sub txtValueItem3_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = PaidOutValueExit(3)

End Sub
Private Sub txtValueItem4_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = PaidOutValueExit(4)

End Sub
Private Sub txtValueItem5_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = PaidOutValueExit(5)

End Sub
Function PaidOutValueExit(TheRow As Integer) As Boolean


PaidOutValueExit = False

If Me("txtValueItem" & TheRow).Value = "" Then
If Me("txtItem" & TheRow).Value <> "" Then
MsgBox "You must enter a valid number."
PaidOutValueExit = True
End If
Exit Function
End If

If Not IsNumeric(Me("txtValueItem" & TheRow).Value) Then
MsgBox ("Entry must be a number.")
PaidOutValueExit = True
Else
Me("txtValueItem" & TheRow).Value = Format(Me("txtValueItem" & TheRow).Value, "Fixed")
FormFile.Sheets("Cashup").Range("PaidOut").Cells(TheRow, 2).Value = Me("txtValueItem" & TheRow).Value

ProcessForm

End If

End Function
'************************************************************************** **
'
' Till Count
'
'************************************************************************** **
Private Sub txtTillCount1_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = TillCountExit(1)

End Sub
'************************************************************************** **
Private Sub txtTillCount2_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = TillCountExit(2)

End Sub
Private Sub txtTillCount3_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = TillCountExit(3)

End Sub
Private Sub txtTillCount4_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = TillCountExit(4)

End Sub
Private Sub txtTillCount5_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = TillCountExit(5)

End Sub
Private Sub txtTillCount6_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = TillCountExit(6)

End Sub
Private Sub txtTillCount7_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = TillCountExit(7)

End Sub
Private Sub txtTillCount8_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = TillCountExit(8)

End Sub
Private Sub txtTillCount9_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = TillCountExit(9)

End Sub
Private Sub txtTillCount10_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = TillCountExit(10)

End Sub
Private Sub txtTillCount11_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = TillCountExit(11)

End Sub
Function TillCountExit(TheRow As Integer) As Boolean


Dim tmpVal As Double


TillCountExit = False

If Me("txtTillCount" & TheRow).Text = "" Then Me("txtTillCount" & TheRow).Text = 0
If Not IsNumeric(Me("txtTillCount" & TheRow).Value) Then
MsgBox "The value you have entered is not a valid number."
TillCountExit = True
Exit Function
End If

If Not WholeNum(Me("txtTillCount" & TheRow).Value) Then
MsgBox "You must enter a valid number."
TillCountExit = True
Else
FormFile.Sheets("Cashup").Range("TillCount").Cells(TheRow, 1).Value = Me("txtTillCount" & TheRow).Value

tmpVal = CInt(Me("txtTillCount" & TheRow).Value) * TillArray(TheRow - 1)
Me("lblTillValue" & TheRow).Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("TillCount").Cells(TheRow, 2).Value = tmpVal

ProcessForm

End If

End Function
'************************************************************************** **
'
' Float
'
'************************************************************************** **
Private Sub txtNumFloat1_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = NumFloatExit(1)

End Sub
Private Sub txtNumFloat2_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = NumFloatExit(2)

End Sub
Private Sub txtNumFloat3_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = NumFloatExit(3)

End Sub
Private Sub txtNumFloat4_Exit(ByVal Cancel As MSForms.ReturnBoolean)


Cancel = NumFloatExit(4)

End Sub
Function NumFloatExit(TheRow As Integer) As Boolean


Dim tmpVal As Double


NumFloatExit = False

If Me("txtNumFloat" & TheRow).Text = "" Then Me("txtNumFloat" & TheRow).Text = 0
If Not IsNumeric(Me("txtNumFloat" & TheRow).Value) Then
MsgBox "The value you have entered is not a valid number."
NumFloatExit = True
Exit Function
End If

If Not WholeNum(Me("txtNumFloat" & TheRow).Value) Then
MsgBox "You must enter a valid number."
NumFloatExit = True
Else
FormFile.Sheets("Cashup").Range("Float").Cells(TheRow, 2).Value = Me("txtNumfloat" & TheRow).Value

tmpVal = CInt(Me("txtNumFloat" & TheRow).Value) * FloatArray(TheRow - 1)
Me("lblValueFloat" & TheRow).Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("Float").Cells(TheRow, 3).Value = tmpVal

ProcessForm

End If

End Function
'************************************************************************** **
'
' Others
'
'************************************************************************** **
Private Sub txtFloatIn_Exit(ByVal Cancel As MSForms.ReturnBoolean)


If Me("txtFloatIn").Text = "" Then Me("txtFloatIn").Text = 0
If Not IsNumeric(Me("txtFloatIn").Value) Then
MsgBox "You must enter a valid number."
Cancel = True
Else
Me("txtFloatIn").Text = Format(Me("txtFloatIn").Value, "Fixed")
FormFile.Sheets("Cashup").Range("FloatIn").Cells(1, 1).Value = Me("txtFloatIn").Value
End If

ProcessForm

End Sub
Private Sub txtCashSalesPOS_Exit(ByVal Cancel As MSForms.ReturnBoolean)


If Me("txtCashSalesPOS").Text = "" Then Me("txtCashSalesPOS").Text = 0
If Not IsNumeric(Me("txtCashSalesPOS").Value) Then
MsgBox "You must enter a valid number."
Cancel = True
Else
Me("txtCashSalesPOS").Text = Format(Me("txtCashSalesPOS").Value, "Fixed")
FormFile.Sheets("Cashup").Range("CashPOS").Cells(1, 1).Value = Me("txtCashSalesPOS").Value
End If

ProcessForm

End Sub
Private Sub txtCCMachine_Exit(ByVal Cancel As MSForms.ReturnBoolean)


If Me("txtCCMachine").Text = "" Then Me("txtCCMachine").Text = 0
If Not IsNumeric(Me("txtCCMachine").Value) Then
MsgBox "You must enter a valid number."
Cancel = True
Else
Me("txtCCMachine").Text = Format(Me("txtCCMachine").Value, "Fixed")
FormFile.Sheets("Cashup").Range("CCMachine").Cells(1, 1).Value = Me("txtCCMachine").Value
End If

ProcessForm

End Sub
'************************************************************************** **
Private Sub txtCCPOS_Exit(ByVal Cancel As MSForms.ReturnBoolean)


If Me("txtCCPOS").Text = "" Then Me("txtCCPOS").Text = 0
If Not IsNumeric(Me("txtCCPOS").Value) Then
MsgBox "You must enter a valid number."
Cancel = True
Else
Me("txtCCPOS").Text = Format(Me("txtCCPOS").Value, "Fixed")
FormFile.Sheets("Cashup").Range("CCPOS").Cells(1, 1).Value = Me("txtCCPOS").Value
End If

ProcessForm

End Sub
'************************************************************************** **
'
' Process the form
'
'************************************************************************** **
Sub ProcessForm()


Dim tmpVal As Double
Dim x As Integer


' Paid Out
tmpVal = 0
For x = 1 To 5
If Me("txtValueItem" & x).Value <> "" Then
tmpVal = tmpVal + CDbl(Me("txtValueItem" & x).Value)
End If
Next

Me("lblCashPaidOut").Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("CashPaidOut").Cells(1, 1).Value = tmpVal
Me("lblPlusPaidOut").Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("PlusPaidOut").Cells(1, 1).Value = tmpVal


' Till Count
tmpVal = 0
For x = 11 To 1 Step -1
If Me("lblTillValue" & x).Caption <> "" Then
tmpVal = tmpVal + CDbl(Me("lblTillValue" & x).Caption)
If x = 6 Then
Change = tmpVal
End If
End If
Next

Me("lblCashInTill").Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("CashInTill").Cells(1, 1).Value = tmpVal

Me("lblChange").Caption = Format(Change, "Fixed")
FormFile.Sheets("Cashup").Range("Change").Cells(1, 1).Value = Change


' Cash Over/Short
tmpVal = 0
If Me("lblCashInTill").Caption <> "" Then
tmpVal = CDbl(Me("lblCashInTill").Caption)
End If
If Me("txtFloatIn").Value <> "" Then
tmpVal = tmpVal - CDbl(Me("txtFloatIn").Value)
End If
If Me("lblPlusPaidOut").Caption <> "" Then
tmpVal = tmpVal - CDbl(Me("lblPlusPaidOut").Caption)
End If
Me("lblActualCash").Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("ActualCash").Cells(1, 1).Value = tmpVal
tmpVal = 0
If Me("txtCashSalesPOS").Value <> "" And Me("lblActualCash").Caption <> "" Then
tmpVal = CDbl(Me("txtCashSalesPOS").Value) - CDbl(Me("lblActualCash").Caption)
If tmpVal >= 0 Then
Me("lblCashOverShort").Caption = Format(tmpVal, "Fixed")
Me("lblCashOverShort").ForeColor = &H80000012
Else
Me("lblCashOverShort").Caption = "(" & Format(tmpVal, "Fixed") & ")"
Me("lblCashOverShort").ForeColor = &HFF
End If
FormFile.Sheets("Cashup").Range("CashOverShort").Cells(1, 1).Value = tmpVal
End If

' Card Over/Short
tmpVal = 0
If Me("txtCCMachine").Value <> "" And Me("txtCCPOS").Value <> "" Then
tmpVal = CDbl(Me("txtCCMachine").Value) - CDbl(Me("txtCCPOS").Value)
If tmpVal >= 0 Then
Me("lblCCOverShort").Caption = Format(tmpVal, "Fixed")
Me("lblCCOverShort").ForeColor = &H80000012
Else
Me("lblCCOverShort").Caption = "(" & Format(tmpVal, "Fixed") & ")"
Me("lblCCOverShort").ForeColor = &HFF
End If
FormFile.Sheets("Cashup").Range("CCOverShort").Cells(1, 1).Value = tmpVal
End If

' Float
tmpVal = 0
For x = 1 To 4
If Me("lblValueFloat" & x).Caption <> "" Then
tmpVal = tmpVal + CDbl(Me("lblValueFloat" & x).Caption)
End If
Next
tmpVal = tmpVal + Change

Me("lblFloatOut").Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("FloatOut").Cells(1, 1).Value = tmpVal

' Summary
If Me("txtCashSalesPOS").Value <> "" Then
Me("lblSummaryCash").Caption = Format(Me("txtCashSalesPOS").Value, "Fixed")
FormFile.Sheets("Cashup").Range("SummaryCash").Cells(1, 1).Value = tmpVal
End If
If Me("txtCCMachine").Value <> "" Then
Me("lblSummaryCard").Caption = Format(Me("txtCCMachine").Value, "Fixed")
FormFile.Sheets("Cashup").Range("SummaryCard").Cells(1, 1).Value = tmpVal
End If
If Me("lblSummaryCash").Caption <> "" And Me("lblSummaryCard").Caption <> "" Then
tmpVal = CDbl(Me("lblSummaryCash").Caption) + CDbl(Me("lblSummaryCard").Caption)
Me("lblTurnover").Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("Turnover").Cells(1, 1).Value = tmpVal
End If

' Cash to Bank
If Me("lblActualCash").Caption <> "" And Me("lblFloatOut").Caption <> "" Then
tmpVal = CDbl(Me("lblActualCash").Caption) - CDbl(Me("lblFloatOut").Caption)
Me("lblCashToBank").Caption = Format(tmpVal, "Fixed")
FormFile.Sheets("Cashup").Range("CashToBank").Cells(1, 1).Value = tmpVal
End If

End Sub
'************************************************************************** **
'
' Init
'
'************************************************************************** **
Private Sub UserForm_Activate()

Dim x As Integer


TillArray(0) = 200
TillArray(1) = 100
TillArray(2) = 50
TillArray(3) = 20
TillArray(4) = 10
TillArray(5) = 5
TillArray(6) = 2
TillArray(7) = 1
TillArray(9) = 0.5
TillArray(9) = 0.2
TillArray(10) = 0.1

FloatArray(0) = 100
FloatArray(1) = 50
FloatArray(2) = 20
FloatArray(3) = 10

Me("lblDate").Caption = DisplayDate

GetYesterday

If EditThisDate Then
PopulateForm
Else
' Get the standard float always
For x = 1 To 4
Me("lblStandard" & x).Caption = Format(FormFile.Sheets("Cashup").Range("Float").Cells(x, 1).Value, "#0")
Next
End If


End Sub
'************************************************************************** **
Sub PopulateForm()


Dim x As Integer


Me("lblDate").Caption = FormFile.Sheets("Cashup").Range("Date").Cells(1, 1).Value
Me("txtStaff").Text = FormFile.Sheets("Cashup").Range("Staff").Cells(1, 1).Value
For x = 1 To 5
Me("txtItem" & x).Text = FormFile.Sheets("Cashup").Range("PaidOut").Cells(x, 1).Value
Me("txtValueItem" & x).Text = Format(FormFile.Sheets("Cashup").Range("PaidOut").Cells(x, 2).Value, "Fixed")
Next
Me("lblCashPaidOut").Caption = Format(FormFile.Sheets("Cashup").Range("CashPaidOut").Cells(1, 1).Value, "Fixed")
For x = 1 To 11
Me("txtTillCount" & x).Text = Format(FormFile.Sheets("Cashup").Range("TillCount").Cells(x, 1).Value, "#0")
Me("lblTillValue" & x).Caption = Format(FormFile.Sheets("Cashup").Range("TillCount").Cells(x, 2).Value, "Fixed")
Next
Me("lblCashInTill").Caption = Format(FormFile.Sheets("Cashup").Range("CashInTill").Cells(1, 1).Value, "Fixed")
Me("txtFloatIn").Text = Format(FormFile.Sheets("Cashup").Range("FloatIn").Cells(1, 1).Value, "Fixed")
Me("lblPlusPaidOut").Caption = Format(FormFile.Sheets("Cashup").Range("PlusPaidOut").Cells(1, 1).Value, "Fixed")
Me("lblActualCash").Caption = Format(FormFile.Sheets("Cashup").Range("ActualCash").Cells(1, 1).Value, "Fixed")
Me("txtCashSalesPOS").Text = Format(FormFile.Sheets("Cashup").Range("CashPOS").Cells(1, 1).Value, "Fixed")
Me("lblCashOverShort").Caption = Format(FormFile.Sheets("Cashup").Range("CashOverShort").Cells(1, 1).Value, "Fixed")
Me("txtCCMachine").Text = Format(FormFile.Sheets("Cashup").Range("CCMachine").Cells(1, 1).Value, "Fixed")
Me("txtCCPOS").Text = Format(FormFile.Sheets("Cashup").Range("CCPOS").Cells(1, 1).Value, "Fixed")
Me("lblCCOverShort").Caption = Format(FormFile.Sheets("Cashup").Range("CCOverShort").Cells(1, 1).Value, "Fixed")
For x = 1 To 4
Me("lblStandard" & x).Caption = Format(FormFile.Sheets("Cashup").Range("Float").Cells(x, 1).Value, "#0")
Me("txtNumFloat" & x).Text = Format(FormFile.Sheets("Cashup").Range("Float").Cells(x, 2).Value, "#0")
Me("lblValueFloat" & x).Caption = Format(FormFile.Sheets("Cashup").Range("Float").Cells(x, 3).Value, "Fixed")
Next
Me("lblChange").Caption = Format(FormFile.Sheets("Cashup").Range("Change").Cells(1, 1).Value, "Fixed")
Me("lblFloatOut").Caption = Format(FormFile.Sheets("Cashup").Range("FloatOut").Cells(1, 1).Value, "Fixed")
Me("lblSummaryCash").Caption = Format(FormFile.Sheets("Cashup").Range("SummaryCash").Cells(1, 1).Value, "Fixed")
Me("lblSummaryCard").Caption = Format(FormFile.Sheets("Cashup").Range("SummaryCard").Cells(1, 1).Value, "Fixed")
Me("lblTurnover").Caption = Format(FormFile.Sheets("Cashup").Range("Turnover").Cells(1, 1).Value, "Fixed")
Me("lblCashToBank").Caption = Format(FormFile.Sheets("Cashup").Range("CashToBank").Cells(1, 1).Value, "Fixed")

' Just to be on the safe side, process till count and float count
For x = 1 To 11
TillCountExit (x)
Next
For x = 1 To 4
NumFloatExit (x)
Next

ProcessForm

End Sub

And in the main module


...

Set FormFile = GetWorkbook(FormFileName)
Application.ScreenUpdating = False
FormFile.Activate
ActiveWindow.WindowState = xlMinimized

' Make worksheet accessable to code
FormFile.Sheets("Cashup").Protect UserInterfaceOnly:=True

Application.ScreenUpdating = True
frmCashup.Show vbModal

And

Sub SaveThisFile()

' Save the cashup sheet (with automatic overwrite)
Application.DisplayAlerts = False
Application.ScreenUpdating = True
ActiveWindow.WindowState = xlNormal
FormFile.Save
ActiveWindow.WindowState = xlMinimized
Application.ScreenUpdating = False
Application.DisplayAlerts = True

End Sub

'************************************************************************** **
Sub AllDone()

SaveThisFile

Application.DisplayAlerts = False
ActiveWindow.WindowState = xlNormal
Application.ScreenUpdating = True
FormFile.Close
frmCashup.Hide
Unload frmCashup
Workbooks(1).Close

End Sub

[georgiboy]

So are you opening the file and keeping it in the background for the form to update?

Could you not just open the file once and make changes then close, this way you would not have to mess with window state at all?

How about making the specific window visible = false, that way you wont need to touch the window state:

Global wbName As String

Sub WriteToFile()
Dim FormFile As Workbook
Dim FormFileName As String

Application.ScreenUpdating = False

FormFileName = "C:\Users\test\Desktop\test.xlsx"
Set FormFile = Workbooks.Open(FormFileName)
wbName = FormFile.Name
Windows(wbName).Visible = False

' do stuff
FormFile.Sheets("Sheet1").Protect UserInterfaceOnly:=True
FormFile.Sheets("Sheet1").Range("A1").Value = "tester"


Application.ScreenUpdating = True


End Sub




Sub closer()
Windows(wbName).Visible = True
End Sub

Hope this helps

[macnab]

So are you opening the file and keeping it in the background for the form to update?

Correct. There will be a file created every day. If I keep all the code for verifying the data out of the file it is much smaller. The file is protected so, once created, it can only be viewed.

But the one suggestion works - Windows(wbName).Visible = False does allow the sheet to be updated. Workbook.Visible does not allow the sheet to be updated.

Problem solved, thanks.

[georgiboy]

No problem, just remember to make it visible again before you save otherwise it will open invisible when you try to open it manually. I'm sure you have spotted this but just wanted to point it out.

[macnab]

:thumb