# sometimes vba works sometimes not



## cicada (Mar 23, 2010)

hey everyone i am obviously doing something fundamentally though rudimentary wrong. Previously i entered code using visual basic and it ran beatifully and executed exactly as i wanted didnt save as it was a test run and reentered the same way (yes i am sure) and it does nothing. could someone give me a step by step on how to make the code run tried using help etc but cant find exactly what im looking. previously i didnt do anything just entered it and it started running. please help very frustrating. going backwards. thanks

Cicada


----------



## RoryA (Mar 23, 2010)

It would help if we knew what the code was. From what you say, my best guess would be that it is event code that is supposed to run in response to you doing something and you have probably entered it into the wrong place (such as a normal module instead of a worksheet or ThisWorkbook module.)


----------



## cicada (Mar 25, 2010)

thanks for the reply Rory. In fact im now having trouble getting anything to run. as you guessed i am running a change evnt procedure though i am pretty sure i am running it from a sheet object in a private module. I think i am doing something wrong i am new to all this. i am not interested in the old code anymore and am interested in your thoughts on the following code (that I also cant get to function



Private Sub Worksheet_Change(ByVal Target As Range)
With ThisWorkbook.Sheets("HILO_TURBO")
If Target <> Range("J7")Then Exit Sub
    If Len(.Range("J7").Value) = 6 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("D3:D6").Value = .Range("J19:J22").Value
    If Len(.Range("J7").Value) = 10 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("G3:G6").Value = .Range("J27:J30").Value
    If Len(.Range("J7").Value) = 14 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("J3:J6").Value = .Range("J34:J37").Value
    If Len(.Range("J7").Value) = 18 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("M3:M6").Value = .Range("J41:J44").Value
    If Len(.Range("J7").Value) = 22 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("P3:P6").Value = .Range("J48:J51").Value
    If Len(.Range("J7").Value) = 26 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("D9:D12").Value = .Range("J55:J58").Value
    If Len(.Range("J7").Value) = 30 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("G9:G12").Value = .Range("J62:J65").Value
    If Len(.Range("J7").Value) = 34 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("J9:J12").Value = .Range("J69:J72").Value
    If Len(Range("J7").Value) = 38 And (.Range("E40").Value > 0) Then .Parent.Sheets("sheet1").Range("M9:M12").Value = .Range("J76:J79").Value
End If
End With
End Sub
First of all a bit of background i have a workbook the main sheet (excuse technical language there probably is another word) is constantly updated with data from an external source. When certain conditions are met i would like for certain ranges on that sheet to be copied to sheet 1 and if those conditions are met again in subsequent updates for the old values on sheet one to be replaced with the new values. hope this is clear. firstly does this code look ok and secondly where would i put it and thirdly and i think im missing something here once entered do i press anything to effect it. in the past i have been pressing butttons with mixed results obviously sometimes having code work sometimes not. thanks for you help

Regards
Cicada


----------



## RoryA (Mar 25, 2010)

That code belongs in the code module for the sheet where you are changing the value of J7. It could be simplified and corrected to:

```
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wks As Worksheet
    If Intersect(Target, Range("J7")) Is Nothing Then Exit Sub
    
    With ThisWorkbook.Sheets("HILO_TURBO")
        Set wks = .Parent.Sheets("sheet1")
        If .Range("E40").Value > 0 Then
            Select Case Len(.Range("J7").Value)
                Case 6
                    wks.Range("D3:D6").Value = .Range("J19:J22").Value
                Case 10
                    wks.Range("G3:G6").Value = .Range("J27:J30").Value
                Case 14
                    wks.Range("J3:J6").Value = .Range("J34:J37").Value
                Case 18
                    wks.Range("M3:M6").Value = .Range("J41:J44").Value
                Case 22
                    wks.Range("P3:P6").Value = .Range("J48:J51").Value
                Case 26
                    wks.Range("D9:D12").Value = .Range("J55:J58").Value
                Case 30
                    wks.Range("G9:G12").Value = .Range("J62:J65").Value
                Case 34
                    wks.Range("J9:J12").Value = .Range("J69:J72").Value
                Case 38
                    wks.Range("M9:M12").Value = .Range("J76:J79").Value
            End Select
        End If
    End With
End Sub
```


----------



## cicada (Mar 26, 2010)

thanks for the code rory looks really good. I did all that you told me and still the code is not running. have checked security settings etc and still it doesnt want to cooperate. would it matter that J7 is a comma delimited string dont think so but worth the question. step by step this is what i am doing in HiLo_Turbo going to developer, clicking on visual basic editor, under microsoft excel objects clicking on HiLo_Turbo then entering code in page that comes up that does have some other stuff on it. after entering do i need to press run sub user form, when i do it doesnt work it brings up a pop up that has a list other macros created by developer. any clues. Thankyou for your time much appreciated.

Regards
Cicada


----------



## RoryA (Mar 26, 2010)

The best way to make sure the code is in the right place is to right-click the worksheet tab and choose View Code. If the code appears, then it's in the right module. If not, then it isn't. I assume you have enabled macros when opening the workbook?


----------



## cicada (Mar 26, 2010)

have enabled macros and when i do as instructed it brings up the code. no idea whats wrong. could there e something else. thanks

cicada


----------



## RoryA (Mar 26, 2010)

Change it to this so we can see if it's even running:

```
Private Sub Worksheet_Change(ByVal Target As Range)
   Msgbox "Change event firing!"
    Dim wks As Worksheet
    If Intersect(Target, Range("J7")) Is Nothing Then Exit Sub
    
    With ThisWorkbook.Sheets("HILO_TURBO")
        Set wks = .Parent.Sheets("sheet1")
        If .Range("E40").Value > 0 Then
            Select Case Len(.Range("J7").Value)
                Case 6
                    wks.Range("D3:D6").Value = .Range("J19:J22").Value
                Case 10
                    wks.Range("G3:G6").Value = .Range("J27:J30").Value
                Case 14
                    wks.Range("J3:J6").Value = .Range("J34:J37").Value
                Case 18
                    wks.Range("M3:M6").Value = .Range("J41:J44").Value
                Case 22
                    wks.Range("P3:P6").Value = .Range("J48:J51").Value
                Case 26
                    wks.Range("D9:D12").Value = .Range("J55:J58").Value
                Case 30
                    wks.Range("G9:G12").Value = .Range("J62:J65").Value
                Case 34
                    wks.Range("J9:J12").Value = .Range("J69:J72").Value
                Case 38
                    wks.Range("M9:M12").Value = .Range("J76:J79").Value
            End Select
        End If
    End With
End Sub
```

and just to check - you need to be changing J7 manually, not as the result of a formula.


----------



## cicada (Mar 27, 2010)

hi rory, sorry didnt respond right away, J7 is not data that i insert myself it changes automatically. It is updated every 10 seconds or so and its contents are determined by an external source. it is simply a comma delimited string that can contain from 0 to 13 numbers. does this impact on the code. thanks again for your time. 

Cicada


----------



## cicada (Mar 27, 2010)

ok so i entered data into j7 manually and nothing happened. assuming that when i enter number etc with length of six charcters i should get numbers appearing in sheet1 range D3:D6 from range J19:J22. nothing happening. no idea. any ideas. thanks again.

Cicada


----------



## cicada (Mar 23, 2010)

hey everyone i am obviously doing something fundamentally though rudimentary wrong. Previously i entered code using visual basic and it ran beatifully and executed exactly as i wanted didnt save as it was a test run and reentered the same way (yes i am sure) and it does nothing. could someone give me a step by step on how to make the code run tried using help etc but cant find exactly what im looking. previously i didnt do anything just entered it and it started running. please help very frustrating. going backwards. thanks

Cicada


----------



## cicada (Mar 27, 2010)

disregard the above message.

I just entered the data manually in J7 and got the pop up box "change  event firing" I simulated case 6 and the figues in range j19:J22 went in  range d3:D6 as desired. but it only works when i do this manually. as  mentioned above the data in J7 is normally entered via an external  source. Is there something i need to do. thanks again.
Cicada


----------



## cicada (Mar 29, 2010)

Does anyone have an answer to this. If the code is working when manually prompted but not otherwise what needs to be done to rectify this. I should remind you that normally J7 is filled with a comma delimited string that comes from an external source that updates every ten seconds or so. when running like this the code does not want to work. When i enter data in the column manually and the manually entered data satisfies the conditons set out in the code it works beautifully. So could anyone please advise on a way to fix this. thanks for your help.

Cicada


----------



## Domski (Mar 29, 2010)

You might need to use a Worksheet Calculate event rather than a Change one.

Dom


----------



## cicada (Mar 29, 2010)

hi domski,

From previous posts several other vba experienced members advised in my situation i needed event change, as in previous code i was using event calculate but they could not get it to run. If i used the above code just under event calculate and changed nothing else would it run, and in which module would i put it. Do have any ideas why as it exists under event change it only runs when manually prompted. thanks

cicada


----------



## cicada (Mar 30, 2010)

ah, i have read that event change procedures will not fire with data from a dde external link although some contend it is possible with versions 2000 onwards. So they suggest entering simple formula in adjacent column such as in k7 type = J7 and use calculate event procedure to run code. just about to try it out, will post for anyone with similar problem to say whether this solution worked. thankyou everyone for your input. will no doubt have more questions later. maybe one day i can contribute. thanks

cicada


----------



## cicada (Mar 31, 2010)

ok did not work!!! this has been driving me nuts cant get anything to happen. I have been working on this for ages. The problem is that it is working of an external dde link so when manually prompted works great if dde link feeds than it doesnt at all. that is for teh change procedure. the calculate procedure i cant get it to work. I have been reading about setlinkondata though it was a little scary for someone of my experience would this be a workaround to my problem that is would my change procedure fire through this. Please help. Yes i am begging. thanks

cicada


----------

