How do I activate a macro on one worsheet by inputing a date in another?

[boudjamr]

Ok, I have a worksheet(worksheet ABC) that I copy data into from an external source (website), in order to get a Vlookup to work I'm using a Trim macro. It works fine if I manually run it, but I'd like to activate it by entering a date in a seperate worksheet (worksheet Exam date). I've tried a couple of suggestions from other posts, but so far nothing...... I don't have much experience with VB, macros, etc.......so any help would be greatly appreciated.

 

[JLGWhiz]

Ok, I have a worksheet(worksheet ABC) that I copy data into from an external source (website), in order to get a Vlookup to work I'm using a Trim macro. It works fine if I manually run it, but I'd like to activate it by entering a date in a seperate worksheet (worksheet Exam date). I've tried a couple of suggestions from other posts, but so far nothing...... I don't have much experience with VB, macros, etc.......so any help would be greatly appreciated.

If you would copy the macro and post it, it would be much easier to give you a good solution. Also, include information about where the macro is stored, i.e. Module1, Sheet1 code module, etc.

 

[boudjamr]

This is the macro.......it is located on Sheet3 (code module) of my workbook.........I would like to be able to import data to Sheet3 and have the macro activated once a date is entered on Sheet4.

Sub TrimVisible2()
Dim s As Long, R1 As Range, c As Range
s = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set R1 = ActiveSheet.UsedRange.SpecialCells(xlConstants, xlTextValues).SpecialCells(xlCellTypeVisible)
R1.Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart
For Each c In R1
c.Value = Application.WorksheetFunction.Trim(c.Value)
Next c
Application.ScreenUpdating = True
Application.Calculation = s
End Sub

 

[JLGWhiz]

Copy the Macro below to the standard code module1. Access that code module by pressing Alt+F11.

Sub TrimVisible2()
Dim R1 As Range, c As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set R1 = Sheets("Sheet3").UsedRange.SpecialCells(xlConstants, xlTextValues).SpecialCells(xlCellTypeVisible)
R1.Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart
 For Each c In R1
  c.Value = Application.WorksheetFunction.Trim(c.Value)
 Next c
On Error GoTo 0
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Then copy this macro to the Sheet4 code module. Access sheet code module by right click sheet name tab, then click view code in the pop up menu. Since you did not specify where the date would be entered on Sheet4, the code below uses column A. You will need to change that part of the code to make it the correct entry location.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then 'Edit date entry range here
    If Not IsDate(Target.Value) Then
        MsgBox "Date Entry Required"
        Exit Sub
    End If
    TrimVisible2
End If
End Sub

 

[boudjamr]

Thanks for the help.......I tried the macros and still won't work.....any suggestions?

 

[boudjamr]

Sorry for the previous post........tinkered and macros are working great........Thanks!!!!

 

[JLGWhiz]

Sorry for the previous post........tinkered and macros are working great........Thanks!!!!

You're welcome
Regards, JLG