Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell Change event VBA

    Hi,

    I having a excel form, where user can apply for products using excel sheet.

    Here I need to enable or disable particular cells on select (option) change. How it can be done in VBA.

    Thanks for your reply..

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Welcome to the lounge as a new poster.

    Here's some VBA code you can adapt to your situation that uses the Worksheet_Change event and the Intersect() function.
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    '*** Limiting the Worksheet_Change event to a firing when a single cell is changed
    
       Dim isect As Range
       
       Set isect = Application.Intersect(Range("A1"), Target)
       If isect Is Nothing Then
         MsgBox "Ranges do not intersect"
       Else
         MsgBox "A1 Changed", vbOKOnly + vbInformation, "Cell Changed"
       End If
    
    End Sub
    Post back if you have questions.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Mar 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thank you RG,

    I am looking somewhat like this:

    Dim Link_To As Range

    If [Q2] = "Select_Link_To" Then

    ActiveSheet.Unprotect ("PASSWORD")

    [R2].Locked = True
    [S2].Locked = True
    [T2].Locked = True
    [U2].Locked = True
    [V2].Locked = True

    ActiveSheet.Protect ("PASSWORD")

    End If


    If [Q2] = "Advance" Then

    ActiveSheet.Unprotect ("PASSWORD")

    [S2].Locked = True
    [T2].Locked = True
    [U2].Locked = True
    [V2].Locked = True

    ActiveSheet.Protect ("PASSWORD")


    Else

    ActiveSheet.Unprotect ("PASSWORD")

    [S2].Locked = False
    [T2].Locked = False
    [U2].Locked = False
    [V2].Locked = False

    ActiveSheet.Protect ("PASSWORD")

    End If

    If [Q2] = "Booked Tickets" Then

    ActiveSheet.Unprotect ("PASSWORD")

    [R2].Locked = True

    ActiveSheet.Protect ("PASSWORD")


    Else

    ActiveSheet.Unprotect ("PASSWORD")

    [R2].Locked = False

    'Optional, reprotect sheet

    ActiveSheet.Protect ("PASSWORD")

    End If

    End Sub

  4. #4
    New Lounger
    Join Date
    Mar 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thank you RG,

    I am looking somewhat like this:

    Dim Link_To As Range

    If [Q2] = "Select_Link_To" Then

    ActiveSheet.Unprotect ("PASSWORD")

    [R2].Locked = True
    [S2].Locked = True
    [T2].Locked = True
    [U2].Locked = True
    [V2].Locked = True

    ActiveSheet.Protect ("PASSWORD")

    End If


    If [Q2] = "Advance" Then

    ActiveSheet.Unprotect ("PASSWORD")

    [S2].Locked = True
    [T2].Locked = True
    [U2].Locked = True
    [V2].Locked = True

    ActiveSheet.Protect ("PASSWORD")


    Else

    ActiveSheet.Unprotect ("PASSWORD")

    [S2].Locked = False
    [T2].Locked = False
    [U2].Locked = False
    [V2].Locked = False

    ActiveSheet.Protect ("PASSWORD")

    End If

    If [Q2] = "Booked Tickets" Then

    ActiveSheet.Unprotect ("PASSWORD")

    [R2].Locked = True

    ActiveSheet.Protect ("PASSWORD")


    Else

    ActiveSheet.Unprotect ("PASSWORD")

    [R2].Locked = False

    'Optional, reprotect sheet

    ActiveSheet.Protect ("PASSWORD")

    End If

    End Sub

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    I'm a little confused by the logic of the above.
    Your control cell is Q2 correct?
    Since Q2 can only have one value I have a problem with the above logic that checks it more than once with an Else condition as the settings of the Else condition can be wiped out by the subsequent If statement.
    This really auto to be coded as a Select Case statement.
    Code:
        ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _
            Scenarios:=False, UserInterfaceOnly:=False,Password:="password"
    
     Select Case [Q2].value
         Case "Select_Link_To"
              {appropriate actions here}
         Case "Advance"
              {appropriate actions here}
         Case "Booked Tickets"
              {appropriate actions here}
         Case Else
              {appropriate actions here}
    End Select
    
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
            Scenarios:=True, UserInterfaceOnly:=True,Password:="password"
    The code can be placed in the 1st routine I posted in place of the message box "Cell Changed" and change the "A1" in the intersect to "Q2". When working you probably want to kill the "Ranges do not intersect" message.
    Last edited by RetiredGeek; 2012-03-29 at 10:50.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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