Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a Code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a Code
Hey Jim...can't get it to work. To make it simple for me...I want the first
part of the macro too run if the cell f2 in the worksheet "new filings!" has a value = 0. If not, I want the part with sheets "T" and "T-1" to run... Do yu have any suggestions? I really appreciate yur help and the time devoted to helpnig me out with this. thanks, Alex "Jim Thomlinson" wrote: This is event code so it will not appear in the play list. Any procedure that requires an input (in this case Target as Range) will not appear in the play list as it can't run without an input value supplied. To fire the code change any value on the sheet in which this code resides and the code should run. To confirm it is running add a message box something like this... Private Sub Worksheet_Change(ByVal Target As Range) msgbox Target.address & " was changed." 'now your code... end sub -- HTH... Jim Thomlinson "Alex Martins" wrote: "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a Code
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a Code | Excel Programming | |||
Inserting a Code | Excel Programming | |||
inserting variables in code???? | Excel Programming | |||
Code for Inserting Multiple lines | Excel Discussion (Misc queries) | |||
Inserting a shared list from code | Excel Programming |