+ Reply to Thread
Results 1 to 8 of 8

Worksheet_SelectionChange problem

Hybrid View

Potty Ash Worksheet_SelectionChange... 01-31-2020, 12:24 PM
Richard Buttrey Re: Worksheet_SelectionChange... 01-31-2020, 12:44 PM
Potty Ash Re: Worksheet_SelectionChange... 01-31-2020, 01:16 PM
Richard Buttrey Re: Worksheet_SelectionChange... 01-31-2020, 02:05 PM
Potty Ash I've attached the spreadsheet... 02-01-2020, 09:34 AM
Potty Ash Re: Worksheet_SelectionChange... 02-03-2020, 09:40 AM
romperstomper Re: Worksheet_SelectionChange... 02-03-2020, 10:05 AM
Potty Ash Re: Worksheet_SelectionChange... 02-03-2020, 10:46 AM
  1. #1
    Registered User
    Join Date
    10-29-2019
    Location
    Lisbon, Portugal
    MS-Off Ver
    Office 365
    Posts
    5

    Unhappy Worksheet_SelectionChange problem

    Good afternoon everyone,

    Could somebody please help me to get the macros below to work together. I came across this code that logs all changes and additions to cells on a sheet. I then introduced a second macro that prevents access to certain columns on the sheet, but unfortunately it will not work because apparently I have introduced two Worksheet Change events. This has got a little bit out of my depth so any help would be greatly appreciated.



    Option Explicit
    Public OldValue As String
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim sSheetName As String
            sSheetName = "Reporting"
            Sheets("Log Details").Unprotect Password:="Emsa1249!"
            Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = ActiveSheet.Name & " ? " & Target.Address(0, 0)
            Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = OldValue
            Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Target.Value
            Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Environ("username")
            Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Now
            Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(0, 5).Hyperlinks.Add Anchor:=Sheets("Log Details").Range("A" & Rows.Count).End(xlUp).Offset(0, 5), _
            Address:="", SubAddress:=sSheetName & "!" & Target.AddressLocal, TextToDisplay:=sSheetName & "!" & Target.AddressLocal
            Sheets("Log Details").Columns("A:F").AutoFit
            Application.EnableEvents = False
            Application.EnableEvents = True
            Sheets("Log Details").Protect Password:="*******"
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        On Error Resume Next
        OldValue = Target.Value
    End Sub
    The following macro is just what I want but it only works on its own.

    'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Column = 3 Or Target.Column = 6 Or Target.Column = 9 Then
                Beep
                Cells(Target.Row, Target.Column).Offset(0, 1).Select
        End If
    End Sub

    Kind regards,

    Potty Ash
    Last edited by Potty Ash; 02-03-2020 at 11:03 AM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Worksheet_SelectionChange problem

    It all looks OK to me. There's no reason why the two change events can't co-exist. Presumably the two change events are on the same sheet?

    Upload the workbook for to allow for a more definitive answer.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    10-29-2019
    Location
    Lisbon, Portugal
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Worksheet_SelectionChange problem

    Hi Richard,

    Thanks for responding.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Worksheet_SelectionChange problem

    In the workbook you've uploaded the Sheet selection change event below is different to the one in post #1 below.

    i.e.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        On Error Resume Next
        OldValue = Target.Value
        'LockFields Target
        'Application.EnableEvents = True
        'If Not Intersect(Target, Range("C1:C10")) Is Nothing Then _
            'MsgBox "Column locked for editing"
    End Sub
    If you reinstate the code in post#1 it wll work as you expect.

  5. #5
    Registered User
    Join Date
    10-29-2019
    Location
    Lisbon, Portugal
    MS-Off Ver
    Office 365
    Posts
    5
    I've attached the spreadsheet with the correct code, but it gives the following message:

    Compile error:

    Ambiguous name detected: Worksheet_SelectionChange
    Last edited by Potty Ash; 02-03-2020 at 09:08 AM.

  6. #6
    Registered User
    Join Date
    10-29-2019
    Location
    Lisbon, Portugal
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Worksheet_SelectionChange problem

    I've attached the spreadsheet with the correct code, but it gives the following message:

    Compile error:

    Ambiguous name detected: Worksheet_SelectionChange

  7. #7
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,143

    Re: Worksheet_SelectionChange problem

    Combine the two into one:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Column = 3 Or Target.Column = 6 Or Target.Column = 9 Then
                Beep
                Cells(Target.Row, Target.Column).Offset(0, 1).Select
    
        OldValue = Selection.Value
        Else
        On Error Resume Next
        OldValue = Target.Value
        End If
    End Sub
    Everyone who confuses correlation and causation ends up dead.

  8. #8
    Registered User
    Join Date
    10-29-2019
    Location
    Lisbon, Portugal
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Worksheet_SelectionChange problem

    Thanks Rory works perfect now!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Worksheet_SelectionChange event problem
    By lynnsong986 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-17-2020, 12:04 PM
  2. [SOLVED] Any way around Worksheet_selectionChange
    By banaanas in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-16-2015, 12:08 AM
  3. [SOLVED] Worksheet_SelectionChange problem - Drop down list
    By Villalobos in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-18-2014, 11:14 AM
  4. Worksheet_SelectionChange
    By anghel.michael in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-21-2011, 02:43 PM
  5. Problem with Worksheet_SelectionChange
    By TRJJK73 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-21-2009, 04:15 AM
  6. Worksheet_SelectionChange Problem
    By Casey in forum Excel General
    Replies: 2
    Last Post: 09-20-2005, 03:23 PM
  7. problem with Worksheet_SelectionChange
    By Valeria in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-25-2005, 12:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1