Posts by Justin Doward

    Hello MW,

    You can achieve your result by copying the cell from excel and right click pasting into the location you desire by selecting copy link and merge formatting. From there it is just a matter of getting the formatting to work. In Options --> advanced -->
    General there is the option to have the link update when you open the document.



    Hello JT,

    try the following code:



    Hi JB,

    There are no changes occurring in the loop, the values all remain the same and subsequently i never changes from a value of I think 342 and the loop is infinite. I am not really sure what you are attempting to do so cannot really help much more.

    To see what is going on click on the side of your code so a red mark appears in the border, place this within the loop then press play and the code will stop running at that point. Ensure your locals window is open and the value of all your variables will be there. Press F8 to then step through the code (hold F8 to rapidly move through the loops) I added a counter for the loops (x = x + 1) to keep track, but nothing changes with each iteration.

    NB: I have been using VBA a long time and never seen that method of referencing cells before, it is brief but mouse-over with codebreak does not provide the current value, it will also cause problems if you have other than the sheet of interest active (i.e. the code will not know which sheet to reference or I assume that is the case).



    Hi CO,

    I cannot really help with the line 2 error without being able to replicate it, I do not know what would give an error when checking .value2, you could delete the line and test for blanks using the str = x.value2 then as below use if x = vbnullstring then goto Nxt_X... but it may give the same error.

    To start on row 2 just enter a goto into the loop:

    For Each x In rng
    If x.Row = 1 Then GoTo Nxt_X
    code here...
    Next x

    Hi CO,

    Not sure why the first does not work for you, it works fine here.

    The second I should have foreseen, sorry about that. I have rewritten to be more specific. If this one needs to be tailored further please upload an example of your data format. Really it is just about defining the range more precisely.

    Hi CO,

    Not sure if this does what you are after, the first only works on the target, the second works through the range depending which range changed.




    Hi AL,

    I have made a small example using your sheet and an alternative approach.

    Note, I have only built the article number textbox and the listbox. I have used this site:

    The VBA Guide To ListObject Excel Tables
    Learn everything there is to know about manipulating and interacting with ListObjects in VBA. Excel Tables are a cornerstone of spreadsheet design. There are…

    when learning to navigate tables in vba.

    NB: As the thread is solved, send me direct if you have any further questions.



    Hello AL,

    The solution to your 2nd problem is here:

    SetFocus not working on TextBox after AfterUpdate [SOLVED]

    Essentially you need to disable the next textbox in the tab order before setting focus in your code:

    txtZoekOpArtBenaming.Enabled = False

    then include a sub that re-enables the disabled textbox when setfocus occurs:

    Private Sub txtZoekOpArtNr_Enter()
    txtZoekOpArtBenaming.Enabled = True
    End Sub

    This is working for me on the example you sent.

    Please note that placing this second question after your first question is not helpful to the forum, particularly as you have not really shown the solution to your first question. No doubt a moderator will draw your attention to the rules and request you read them again.

    NB: Good luck with the coding, I like the look of the forms. I would suggest the following if you are open to it:


    You are not always using your WITH statements, once you use a with statement you do not then need to name the object again.


    I suggest rewriting the code so that very little of it runs from the form code page, rather have code written in the modules and called from the form page. This generally allows you to write code that is more flexible and can be called from multiple locations.

    3. you can loop through controls using for example:

    with frmKlanten
    for i = 1 to 8
    .controls("TextBox" & i).value = ""
    next i
    end with



    Hello CapG

    Not sure why you would not just do this by seperating the text into two columns and using the vlookup function native to excel, but this code should work for the text as provided:

    Hi Jason,

    I cannot see how the sheets relate, ID 100000 for example disappears in sheet 3, ID 1040000 repeats on sheet 3 except it has brackets around the negative value and no account is taken of the 1459 that is present on sheet 2. ID 2309D is only on sheet 2 and nowhere else.

    Essentially I do not see what is supposed to be occurring.

    Hello UA,

    Try the attached, the code is as below:

    Note that I have made the list an excel table, it is not really necessary just how I have been doing things lately. Just use insert table and highlight your table of interest.

    There is an error thrown that I have not worked out, but the resume next corrects it for the list provided, it may be quite slow on your large database so try it on a few hundred/thousand lines first and see how it goes.

    Copy of SampleDataMediaList.xlsm


    Hi Splat,

    Roy's point is that there is no scenario in which coding to select the cell will be more efficient then just writing to or taking information from the cell directly. Selecting ranges tends to be used by early coders before they learn how to navigate things properly, I believe Roy is trying to determine the overall intent of your coding so he can point you in the right direction but if you are happy that the system does what you are after then stick with it for now, you will probably correct it in the future.

    NB: you can easily load a full excel file here and get a working example returned to you where appropriate, i generally would not download and open a zip file.

    Glad my earlier post helped!



    Hi MA,

    The code you have provided does not really do anything like what you are requesting, is there a reason you are using a combination of system popup and msgbox rather than just using msgbox?

    Do you want a message to be displayed when a cell changes or do you want a macro to run through the ranges and popup/msgbox on all values?