Posts by Justin Doward

    I have used this technique before, and the previous forms I have made do work still, but for some reason whenever I try to open .pdf files with the webbrowser on a form now it crashes. In the attached file if you create a c:\test directory it should list the files in the test directory in the listbox when you select from the combobox, it then works to open jpg, xlsx, doc and whatever else is there except pdf files, when you select them from the listbox.

    The attachment is a stripped down version of my working document, but it has the same issue.Book1.xlsm

    Can anyone let me know why this might not be working, and whether you have the same trouble if you test the file?

    Hi Victor,

    Try this code in the code for the sheet:

    And this code in a module:

    Change your drop down in G9 to reflect the sheet names not their references.

    Let me know how it goes.

    Okay, I think we got there. It makes sense now.

    If this has solved it, can you mark it as the answer on stack exchange as well. Cheers Justin

    Hi Victor,

    Again, I think this is what you are after however I am confused by having the entry cell in H now and the changed cell in G since this means the loop to look for hidden columns is redundant?

    Alternatively you could try a loop like:

    Sub copyRWs()
    Application.ScreenUpdating = False
    Dim a
    a = Application.Transpose(Sheet1.Range("O2:O" & Sheet1.Cells(Rows.Count, "O").End(xlUp).Row)) 'creates array from O2:last row
    For i = LBound(a) To UBound(a)
    Sheet1.Rows(i + 1).EntireRow.copy (Sheets(a(i)).Cells(Sheets(a(i)).Cells(Rows.Count, 14).End(xlUp).Row + 1, 1)) 'copies each row
    Next i
    Application.ScreenUpdating = True
    End Sub

    This assumes you have a table starting on row 1, change sheet1 to your sheetname , if jolivanes nor my response achieve what you are after an example worksheet is probably needed.

    Hi M1,

    You can try this:

    The macro record function is very useful to identify the code required to complete a task but it does not do it efficiently, it records everything including when you scroll down the page or switch sheets etc... It is generally not necessary for these functions to occur when using code so you can go through and delete the scroll, select and activate portions of the code however you then will need to slightly modify the syntax of the code to allow for what you have changed.

    You can often replace 10 lines of recorded macro with a single line once you know the appropriate syntax.

    If you are still after a macro solution, I think this does what you are after - try the button on the sheet.

    SampleTables w macro.xlsb

    Hi Alhagag, you can try this:

    It works on the example you sent.

    Hi HE,

    Your example sheet does not appear to make sense with reference to your question, for example "Date Completed" appears as a field in sheet 3 as column B but column Q from sheet 1 is not one of the columns you are trying to copy from sheet1 (according to your question). Additionally your complaint numbers on sheet2 in no way align with the complaint numbers on sheet1.

    I think I know what you are trying to do, i.e. copy the relevant information for a particular complaint from sheet1 to sheet 3 if there is a line Y in sheet 2 column Z but this is not really clear from the your post.

    This code in the sheet should work to some extent if modified to suit.

    As in the attachment it assumes a unique complaint ID, which may or may not be correct?

    Hi HO,

    You could try something like the attached, the macro is in module 1 of the attachment (as below).

    There is a small button on the summary sheet in A1 to activate the macro.

    forum sample with macro.xlsm

    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:

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

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