Solved Event handler not working in VBA

March 1, 2020 at 18:46:38
Specs: Windows 10
I wrote a code such that we click any where in first column it shoud look for the value and come back and update in same sheet from D3 , The code works fine when I debug but the event handler is not working (i click on cell the value is not updating).below is my code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Application.EnableEvents = True

If ActiveCell.Row > 11 And ActiveCell.Row < 100 Then
If ActiveCell.Column > 1 And ActiveCell.Column > 9 Then
If AciveCell.Value = "" Then
MsgBox "No Competencies"
Else
Call updated
End If
End If
End If

End Sub

Sub updated()

Application.EnableEvents = True

currentrow = ActiveCell.Row
Activecolumn = ActiveCell.Column
lastrowrpt = Sheets("Competency View(2)").Cells(Rows.Count, 4)


If Activecolumn = 1 Then
currentcoulmn = "A"
End If

currentCompetency = Range("A" & currentrow)

ActiveSheet.Range("D3").Select

Do
If IsEmpty(ActiveCell) = False Then
Range(ActiveCell, ActiveCell.Offset(0, 11)).Delete shift:=xlUp
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveSheet.Range("F1") = currentCompetency


Sheets("DefinitionPLList").Activate
Sheets("DefinitionPLList").Range("A1").Select
r = Application.WorksheetFunction.Match(currentCompetency, Sheets("DefinitionPLList").Range("A:A"), 0)
Sheets("DefinitionPLList").Range("A" & r).Select

Competency_Name = ActiveCell.Offset(0, 0)
Definition = ActiveCell.Offset(0, 1)
Foundation = ActiveCell.Offset(0, 2)
Proficient = ActiveCell.Offset(0, 3)
Advanced = ActiveCell.Offset(0, 4)
Expert = ActiveCell.Offset(0, 5)


Sheets("Competency View(2)").Activate
Sheets("Competency View(2)").Range("D3").Select
P = 3
Do

If ActiveCell.Value <> "" Then
ActiveCell.Offset(1, 0).Select
P = ActiveCell.Row
End If
Loop Until ActiveCell.Value = ""

Sheets("Competency View(2)").Range("D" & P) = Competency_Name
Sheets("Competency View(2)").Range("E" & P) = Definition
Sheets("Competency View(2)").Range("F" & P) = Foundation
Sheets("Competency View(2)").Range("G" & P) = Proficient
Sheets("Competency View(2)").Range("H" & P) = Advanced
Sheets("Competency View(2)").Range("I" & P) = Expert

Sheets("DefinitionPLList").Activate
Do

ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value <> currentCompetency

End Sub


See More: Event handler not working in VBA


#1
March 1, 2020 at 19:15:30
✔ Best Answer
First, a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link.

Thanks!

I don't have time to setup a test worksheet right now, but I did read through your code - which will be even easier to do once you format it correctly. Some things jumped out at me:

---->   If ActiveCell.Column > 1 And ActiveCell.Column > 9 Then

Is that right? If the active column is > 9, then obviously it's > 1. Do you mean <9?

---->   If AciveCell.Value = "" Then

I think you need a VBA spellchecker. ;-)

What is this for?

     If Activecolumn = 1 Then
        currentcoulmn = "A"
     End If

Besides the probable misspelling of "currentcoulmn", I don't see where you use that variable any place in the code. Why set it if you don't use it?

Fix those things then come on back. I have some other suggestions but we've got to get what you've written so far working before I confuse the matter by suggesting some efficiency modifications.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
March 1, 2020 at 20:10:10
Thanks for this . Yes I do need a spell check . Sorry :)

Report •

#3
March 2, 2020 at 03:36:55
Is it working now?

Are you planning on reposting the code as explained in the How To?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •
Related Solutions


Ask Question