Consulting

Results 1 to 7 of 7

Thread: Problem writing to minimised workbook - worksheet minimised when saved

  1. #1
    VBAX Regular
    Joined
    Jul 2018
    Posts
    11
    Location

    Problem writing to minimised workbook - worksheet minimised when saved

    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?

  2. #2
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    The problem could be earlier in the code, Can you provide the whole sub that contains the issue?
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  3. #3
    VBAX Regular
    Joined
    Jul 2018
    Posts
    11
    Location
    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

  4. #4
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    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
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  5. #5
    VBAX Regular
    Joined
    Jul 2018
    Posts
    11
    Location
    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.

  6. #6
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    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.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  7. #7
    VBAX Regular
    Joined
    Jul 2018
    Posts
    11
    Location

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •