Posts by Justin Doward

    Hi PO,

    It is a little difficult to workout what you are trying to do exactly, I think from what you have described that you have all of your pictures in a single row? However it is not clear. If this is the case then you would wind up overwriting each file as the code you provided takes the name from the row of the picture and column A. It is not however clear how you do intend to name your files, where is the file name located in reference to the picture?

    An example sheet would of course help.

    Hello, thank you for reading:

    I have a workbook with timed events using Application.Ontime referencing a global variable (GlobalTimer) that either opens a form (called TimeOut), or closes the workbook (to prevent users leaving a workbook open on a networked drive preventing others from using it). The form that opens is a warning that the sheet will close in x minutes unless a response is obtained, the form itself then closes after a few seconds.

    This all works well except if windows is locked in which case the Application.WindowState = xlMaximized and Userform.Show commands do not run until the computer is reopened and if minimised the icon in the command bar clicked on.

    Is there a way to detect if lines have failed/is failing to display the form and subsequently simply close the workbook?

    NB: If I simply want to close the workbook, without giving warning, this works fine even if the computer is locked. it is just the loading of the form while the computer is locked that is causing an issue.

    the code in the workbook is like this:

    The code in the module:

    The code linked to the form:

    1. Private Sub UserForm_Activate()
    2. Dim MyTime As Date
    3. MyTime = Now()
    4. Application.OnTime MyTime + TimeValue("00:00:05"), "ClsTIMEOUT"
    5. End Sub

    There are a couple of buttons on the form that either change the globaltimer or does nothing.

    HI O.

    I have added some basic functionality to your form to give some ideas, note that the paste offset is out sometimes due to the merged cells (I think) so unmerge them to fix it.

    Not really sure if it is what you are after, but there is not much there to work with at this point.

    Book31 edit.xlsm


    HI Carim,

    Your macro was returning 1920 for the year, not sure how to fix it formula wise but the cDate solves the problem once you feed it a format it recognises. I modified your code as follows:

    1. Sub DateFixerV3()
    2. Dim MyStr As String
    3. Dim r As Range
    4. For Each r In Range("A2:A4")
    5. MyStr = Replace(r.Text, ".", "/", 1)
    6. r.Offset(0, 6) = CDate(MyStr)
    7. Next r
    8. End Sub



    Hi WH,

    It probably depends where the hold-up is occurring, if it is during the copy and paste of the ranges then you could try converting them to arrays and then moving the values which should speed things up as outlined in this video:

    If the hold-up is due to the pasting and calculating so many formulas, then do you really need to have the formulas? can you do the calculation and just paste the values or is a user going to be modifying the sheet later? You might be able to use a vba change event for the page to return a value rather than pasting the formulas in the sheet.

    If you post an example of your data I could test the array idea, or you could just comment out the formula paste and use the timer see how long it takes without the formula portion of the code.



    This is an alternative approach using advanced filter and a hidden sheet,

    Note that you do not have to return all 23 rows if you don't want to, if you change the headers on the hidden sheet in the range from D:Z to just the headers you want returned in the listbox autofilter will limit the return to those headers. The criteria for the filter are on the hidden sheet from A:B, just change the headers there to whatever additional criteria you wish to add and adjust the ranges to suit.

    You could also do this with a temp sheet, and delete the sheet each time as Roy has done and this would be more streamlined (and a smaller sized workbook) but you would need to copy the headers to the temp sheet each time so for now I just used the hidden sheet, the only real benefit is that you do not need to press the button between selections.

    VBA MYDATA - EDIT.xlsm



    Hi JO,

    Try the method in the attachment, this method is the only way I know to achieve what you are after i.e. load the data into an array and then set that array to be the list for the listbox.


    Next time just upload the code in the excel file if you want someone to look at the code you are using in context.



    Hi BW,

    I have not tested the idea but you could try a combination of these:

    1. Ask the user you are giving the file to for the computer name they are going to use the file on the use a line in the code like:

    1. if Environ$("computername") <> "Correct Name" then

    2. run code as described here:

    which will delete the file.

    or you could identify the PC in other forms eg:…uter-attritbutes.1054445/

    Otherwise you could simply write into the code that it will not work or will disable the file in other ways (such as deleting ranges and saving the workbook, if it is not on a computer with the correct name, bios serial number or whatever you choose.

    This combined with some password to protect the code and very hidden sheets etc would probably be the best you could get.

    If you do work this out let me know, I would be interested in the solution.



    Hi GE,

    I had a look at your problem but cannot fathom what your needs are, can you clarify what you actually want to happen?

    If you include a sheet with a before and after sort of theme it might help, i.e I click this button and this information goes here, etc...



    Hi Mubby,

    Below are my thoughts based on your post, note that I am not a moderator and feel free to wait for Rob or one of the others to comment.

    If you are interested in getting a job done, rather than learning in the forums, check out the consultancy link:


    Place the post in the HIre Help forum:


    I believe the second will be cheaper but it would depend on your budget, how quickly you need something built, and how much customisation is required. What you appear to be looking for is simple enough but time consuming to tweak everything, you also need to consider how you are going to maintain it into the future if you have no VBA experience yourself.

    If you are not in a hurry, and are looking to learn VBA, post your workbook as you come to discrete problems and people here will point you in the right direction.

    Looking at your code there are a few errors, you define X as the last used row in A then redefine X as the last used row in B (so X will be the last used row in B), you then loop through the A column and refill every text box whenever you get a match with textbox31 (which I assume is your search string) you really need to list the results as they are found and this is usually done using a listbox or combobox.

    As Roy states, a loop search is not the most efficient search method it will do for small databases (up to a few thousand entries, more if you array your data first). The built in Autofilter function of excel can be used as an efficient VBA search, I am not sure what mechanism Roy is using in his linked database.

    A loop is a more efficient way of filling your textboxes though, for example:

    1. For Y = 6 To X
    2. Me.Controls("TextBox" & Y - 5).Text = Sheet1.Cells(Y, 1).Value
    3. Next Y

    would replace lines 8 to 34 in your code.

    As you do not reference a userform in your code, I assume you do not actually have a working form at this point? Feel free to upload what you are working on if you feel like going in that direction.



    It should work, did you change the directory in both spots in the code?

    I have changed the code slightly in the attachment so there is only one spot where you need to change the directory. Are the picture files on you local drive? The code will work for a local drive, it should also work for a networked drive, if it is a web address it will probably require completely different code, test the attachment with a local drive and let me know if it works.



    Hi Ajith,

    This works on the example you sent, change the directory as appropriate:

    Let me know how you go.


    Hi Ajith,

    Don't forget to wrap your code in code tags, and read the rules to ensure you get an answer.

    You should just be able to use something like:

    1. For x = 2 To lastrow
    2. If Dir("\\C:\Users\ajith.nair\Desktop\Range\photos for new range Q1\" & pictname & ".jpg")  = "" Then Goto MyNext
    3. Relevant code
    4. MyNext:
    5. Next x