Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a Code
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a Code | Excel Programming | |||
inserting variables in code???? | Excel Programming | |||
Code for Inserting Multiple lines | Excel Discussion (Misc queries) | |||
Inserting a worksheet into an open workbook via VBA code | Excel Programming | |||
Inserting a shared list from code | Excel Programming |