Sharing a Workbook in Excel for Simultaneous Edit or Modification:

Multiple users can share and modify excel  workbook.  Updated data will be available all the time. You can track changes across network using user’s login name and the date they made update. Workbook however needs to be kept in a shared network. 

           Open your workbook.

               Navigate to Review on the toolbar

               Click on the Share Workbook icon

               Place a check on the check box that says Allow more than one user at the same time.

               Click on OK

               Now, save your workbook on a network share

VBA – Control Arrays

What is a Control Array?

A Control Array is a group of controls that share the same name type and the same event procedures. They are a convenient way to handle groups of controls (Same Type) that perform a similar function. All of the events available to the single control are available to the array of controls.

Can we create Control Arrays in VBA?

Even though, VBA doesn’t allow us to to create Control Array like in vb6 and vb.net, we can still create Control Array in VBA using Class Module.

Why do we need Control Arrays in VBA?

Control Arrays mainly have these advantages

1.     Controls in a Control Array share the same set of event procedures. This results in you writing less amount of code.

2.     Control Arrays uses fewer resources.

3.     You can effectively create new controls at design time, if you need to.

Let’s say you have 10 textboxes in your UserForm (see image below)

And you want all 10 to be numeric textboxes. Numeric textboxes are those text boxes where you can only type numbers.

Now imagine writing this code 10 times for each and every textbox?

This is where we will use Control Array of Textboxes and assign them the same procedure.

To start with add a new Class and name it Class1. Now paste this code in the Code area of Class1 module

Public WithEvents TextBoxEvents As MSForms.TextBox

Private Sub TextBoxEvents_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    Select Case KeyAscii

    '<~~ 48 to 57 is AscII code for numbers. 127 is for 'Delete' and 8 is for 'Backspace'

    Case 48 To 57, 127, 8

    Case Else

        KeyAscii = 0

    End Select

End Sub

and in the UserForm Initialize event, paste this code.

Option Explicit

Dim TextArray() As New Class1

Private Sub UserForm_Initialize()

    Dim i As Integer, TBCtl As Control

    For Each TBCtl In Me.Controls

        If TypeOf TBCtl Is MSForms.TextBox Then

            i = i + 1

            ReDim Preserve TextArray(1 To i)

            Set TextArray(i).TextBoxEvents = TBCtl

        End If

    Next TBCtl

    Set TBCtl = Nothing

End Sub

 For Reference:

http://siddharthrout.wordpress.com/2011/08/05/vba-control-arrays/

Run an .exe file through VBA

Sub test_shell()

Dim program As String, taskid As Integer

program = "C:\Windows\notepad.exe"
taskid = Shell(program, vbNormalFocus)


End Sub

Create Unlocked cells on a protected Sheet

A situation arises when we need to give access to specific cells of a shared excel file depending on UserId.
VBA code for this mentioned below:




Sub specificAccesstocellsinWorksheet()
    If Environ("COMPUTERNAME") = "SOUM-BJS" Then
     
        ActiveSheet.Unprotect
        ThisWorkbook.Sheets(1).Range("B1:B21").Locked = False
        ActiveSheet.Protect

    End If


End Sub

User Defined Types(UDT) for VBA

User Defined Types (UDTs) are a convenient way to store related data in one variable. The most useful way one can use UDT to pass information between procedures. Here is a simple example  of how to use UDT. The type statement is used to define UDT and must be outside of any procedures.

Option Explicit

Private Type Applicant

    firstname As String

    lastname As String

    resumerecvd As Boolean

    interviewdate As Date

End Type

Sub defineUDT()

    Dim myapp As Applicant

    myapp.firstname = "Soumyendu"

    myapp.lastname = "Choudhury"

    myapp.resumerecvd = True

    myapp.interviewdate = #7/15/2011#

    useUDT myapp

    

End Sub

Sub useUDT(myInput As Applicant)

    

        MsgBox myInput.firstname

        MsgBox myInput.lastname

        MsgBox myInput.resumerecvd

    

End Sub

Use of DoEvents in VBA

   DoEvents( )

	Pauses execution to let the system process other events.

REMARKS

	·	The DoEvents function always returns zero.
	·	DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue and all keys in the SendKeys queue have been sent.
	·	DoEvents is most useful for simple things like allowing a user to cancel a process after it has started, for example a search for a file. For long-running processes, yielding the processor is better accomplished by using a Timer or delegating the task to an ActiveX EXE component.. In the latter case, the task can continue completely independent of your application, and the operating system takes case of multitasking and time slicing.
	·	Any time you temporarily yield the processor within an event procedure, make sure the procedure is not executed again from a different part of your code before the first call returns; this could cause unpredictable results. In addition, do not use DoEvents if other applications could possibly interact with your procedure in unforeseen ways during the time you have yielded control.

 For Example:

Do While browser.Busy Or browser.readyState <> READYSTATE_COMPLETE

   DoEvents

UserForm1.Caption = "Record Number:- " & i

   

Loop