Inserting a Code
"Does not run"= Doesn't even appear in the list when I press "play"...any
ideas? I corrected what you said and it does make more sense, thanks!
Thanks for your help!
"Jim Thomlinson" wrote:
Define "Does not run". Does the event fire the code or does the code not run
at all? If the code runs dou you get a run time error or does the code just
not do what it is supposed to do? One thing I do notice that is ver curious
is the line
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
compares the address of the cell that was just changed with the value in F2.
Comparing an address to the value in a cell is not the normall course of
things.
If Target.Address = Worksheets("NEW FILINGS").Range("f2").address Then
would probably make more sense...
--
HTH...
Jim Thomlinson
"Alex Martins" wrote:
Hey Jim. Thanks for your answer. Here's the whole code...maybe this way you
can tell me how to make it work! After I deleted the "Sub new_filings" now I
can't get it to run!
Thanks for all your help.
Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String
strCnn = "URL;" & Worksheets("NEW FILINGS").Range("B6").Text
With Worksheets("NEW FILINGS").QueryTables.Add(Connection:=strCnn,
Destination:=Worksheets("NEW FILINGS").Range("B10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 2
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingRTF
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Else
Dim strCnct As String
strCnct = "URL;" & Worksheets("t").Range("A5").Text
With Worksheets("t").QueryTables.Add(Connection:=strCnc t,
Destination:=Worksheets("t").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Dim strCncts As String
strCncts = "URL;" & Worksheets("t-1").Range("A5").Text
With Worksheets("t-.1").QueryTables.Add(Connection:=strCncts,
Destination:=Worksheets("t-1").Range("A10"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Application.EnableEvents = True
End If
End Sub
"Jim Thomlinson" wrote:
Every sub needs an end sub. You code should end up looking something like this
Sub new_filings()
'Do something in here
end sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Code is placed in "ThisWorkbook"
'I don't think this is the one you want
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'This must be places in the sheet
'Righ Click on tab NEW FILINGS - View Code
if Target.Address = "$F$2" then
else
endif
End Sub
--
HTH...
Jim Thomlinson
"Alex Martins" wrote:
Hi, I have been working on a Macro from some time now, and I find myself in
some trouble.
I need to insert a condition, which I have done accroding to the "events
change" chapter in cpearson.com
The only problem is that my macro code will now start-out like this:
Sub new_filings()
Private Sub Change(ByVal Target As Excel.Range)
If Target.Address = Worksheets("NEW FILINGS").Range("f2") Then
Application.EnableEvents = False
If Target.Value = 0 Then
'Dim strCnn As String
and I can't get around the error message that goes : "expected End Sub" and
it highlights: "Sub new_filings ()"
Thanks in advance for your help
Alex
|