Posts by Justin Doward

  • Form webbrowser crashes opening PDF files.

    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?

  • How can this code be modified to show the result on more sheets based on a condition ?

    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.

  • How can I adapt this code to run in an automatic way without me entering hundreds of lines inside manually ?

    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

  • How can I adapt this code to run in an automatic way without me entering hundreds of lines inside manually ?

    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?


  • Shorter way to copy/paste between sheets

    Alternatively you could try a loop like:


    Code
    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.

  • Delete blank rows and columns in Excel through macros

    Hi M1,


    You can try this:


  • New to VBA

    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.

  • Make one empty row only between tables.

    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

  • decreasing list of many customers contain zero and precede it

    Hi Alhagag, you can try this:


    It works on the example you sent.

  • VBA Code to work across mutli sheet in one workbook

    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?

  • Read column value and return row A value

    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

  • VBA - Export Images from Excel with specific names as column / row

    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.

  • Prevent VBA Application.Ontime running if windows locked.

    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:


    Code
    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.