Macro that adds a code to an existing worksheet to allow a range of cells a double click (today's date) option.

[RomeoPaul]

I know very little about VBA and don't use it on a regular basis to learn much. I normally find code from a Google search, paste it in and modify it to fit my needs. Below simply inserts code from a module to an existing worksheet.

'This inserts code from a module to an existing worksheet
Sub Sample()
Dim wb As Workbook, ws As Worksheet
Dim strProcName As String

Set wb = ThisWorkbook
Set ws = Sheet1

Set VBP = wb.VBProject

strProcName = "Worksheet_SelectionChange"

With wb.VBProject.VBComponents( _
wb.Worksheets(ws.Name).CodeName).CodeModule
.InsertLines Line:=.CreateEventProc("SelectionChange", "Worksheet") + 1, _
String:=vbCrLf & _
" Msgbox ""Hello World"""
End With
End Sub

I don't want any cell to show a message box saying "Hello World"!
Below is the code I found that if pasted into my existing worksheet, does what I actually want. How can I get this to run within the macro above?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("F5:J999")) Is Nothing Then
Cancel = True
Target.Formula = Date
End If
End Sub

Thanks in advance for anyone willing to give this your time.

 

[Perpa]

RomeoPaul,
Welcome to the Forum.
I tried modifying Sample code to load the BeforeDoubleClick code, but got an error message on this line:

Set VBP = wb.VBProject

"Access to VB Project NOT TRUSTED - Error 1004"

I'm not sure what that means exactly, having never used 'VBProject' in my coding before. Perhaps someone else here may better assist you with that.

But in the meantime, since you have experience copying code to the VB Editor:

..."I normally find code from a Google search, paste it in and modify it to fit my needs."

and to get the code working, the simplest thing would be to just copy the BeforeDoubleClick code, then right click on the sheet tab where you want the code, Select 'View Code' which opens the VB Editor, select 'Worksheet' from the dropdown by the word 'General', then paste right over the 2 lines of default code presented so all you have is the 6 lines of the BeforeDoubleClick code. Then close the VB Editor and save your workbook as macro enabled. Done.
Perpa

 

[RomeoPaul]

Thanks for your idea. I don't know what "Set VBP = wb.VBProject" does either, but that macro automatically adds code to the existing worksheet. That's what I need to happen with the "double click" code. Simply pasting it into the existing worksheet is fine for an occasional spreadsheet, but I'd like everyone in my department (who all know less that me about excel) to be able to have this happen automatically when a macro is run.
Our design software will produce a stock list / bill of materials in excel. At that point anyone will run a macro that formats the entire sheet. I'd like the "Double click" option to format 4 columns within that macro.

Thanks again for your time!