CODEWITHSUNDEEP
.netexcelvb.netwinformsvisual-studio-2013
user5538704
user5538704

Reputation: 91

Disable button multiple users update Excel vb.net

I have created a form in Visual Studio 2013 which is used to enter data in Excel by multiple users at same time. It works perfectly fine except if two users open form at the same time or if one has clicked submit and the form is still processing. Then it shows the following error for the other person.

How do I disable the command button so that when one user clicks on submit, for other users the form shows the button disabled till the data is being processed?

EDIT:

Error

Unhandled exception has occurred in your application. Exception from HRESULT: 0x800A03EC

Imports excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

Public Class Form1
Dim xlapp As New excel.Application
Dim workbook As excel.Workbook
Dim worksheet As excel.Worksheet
Private Shared _Saving As Boolean

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
    workbook = xlapp.Workbooks.Open("\2.xlsx")
    xlapp.Visible = False
    worksheet = workbook.Sheets("sheet1")
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click


    If _Saving Then
        MsgBox("wait!")
        Exit Sub
    End If

    _Saving = True
    If workbook.MultiUserEditing Then
        workbook.AcceptAllChanges()
        workbook.Save()
    End If

    Dim row As Long
    Dim alpha As Long = 0
    row = 5
    With worksheet
        Do While .Cells(row, 4).value IsNot Nothing
            row = row + 1
        Loop

        .Cells(row, 2).value = Me.fname.Text
        .Cells(row, 3).value = Me.lname.Text
        Me.fullname.Text = Me.fname.Text + Me.lname.Text
        .Cells(row, 4).value = Me.fullname.Text

    End With
    xlapp.DisplayAlerts = False

    workbook.SaveAs("\2.xlsx", AccessMode:=excel.XlSaveAsAccessMode.xlShared)

    _Saving = False
End Sub

End Class

Upvotes: 0

Views: 108

Answers (1)

Whitey
Whitey

Reputation: 431

One way to do this would be to use a Shared variable to flag when another user is saving to the spreadsheet.

Private Shared _Saving As Boolean

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    If _Saving Then
        'Display a message, form, etc to let this user know someone else is currently saving
        Exit Sub
    End If

    _Saving = True
    'Save code here
    _Saving = False
End Sub

Upvotes: 0

Related Questions