Posts by tbhendralesmana

    Hello all,

    Good day, love the new look for the forum.

    hope this is not repeated question as I could not find the similar one.

    I have an excel with an automated update from SharePoint list, this list provides me data from column A to G (worksheet named as Master Table). The data that I have in column F is generate as text which is most of the time having lot of characters and its turn the cell height become maximum number that excels can provide (409.5) this condition cause a problem that every time I save the file to pdf some of the text is cut and missing from the preview.

    is there any VBA code that could split the text in column F whenever the height is maximum so that the rest of the text will go to the next row.

    I used the autofit function but it is not enough to cover the needs,

    can someone help me out?

    here is my code with autofit :


    Hello Carim,

    oh wow, thank you! that's helped me a lot. I modify the code and it's working.
    I hope it not out of the topic, but after I modify the code another type mismatch error popping up for below code (
    bold is where the error comes):

    1. Sub AddClock(Cell As Range)
    2. Set oTargetCell = Cell
    3. Cell.FormulaR1C1 = "=TEXT(NOW(),""hh:mm:ss"")"
    4. lTimerId = SetTimer(0, 0, 100, [B]AddressOf TimerProc[/B])
    5. End Sub

    any advice?

    Hello everyone,
    been so long not to post but having so much learning process from this forum,
    I need some support to update the macro below, I tried several modify with the VBA but still not solved. Below VBA currently compatible for 32bit user, and now I would need to perform this with 64bit system.
    any help/advice much appreciated.

    this is the error message:


    compile error: The code in this project must be updated for user ono 64-bit system. Please review update Declare statement and then mark them with the PtrSafe attribute

    here is the code (bold is where the error comes):

    thanks a lot :)

    ooohh wow incredible, this is nearly to be perfect, i'm so gratefull...

    Almost there, if more than one file selected, clear master sheet1, then add QRSIS sheet1, (1) then add the next to end of master sheet1, or (2) open new master file and repeat? - if more than 1 file selected than add new tab in next to master sheet, in any condition master file will only open 1 in a time.

    apologize for this silly question, is it possible to include file which is located in download folder?I've tried the code and its showing only files which located in same folder with master file.

    millions thanks :D

    Hi Mr.Kenneth,

    Sorry for the unclear message, please find below answer for your query :
    1. What specific files, xls, xlsx, xlsm, etc.? - the files type will only .xls
    a. One or more? - there could be more than 1 files in the folder. That is why, options to select files need to appear during the process.
    2. I guess they will know what "correct" file means. - the correct files mean only to list the files which is names under QRSIS, the reason why to filter with QRSIS keyword is to gave user option of which files they need, since the complete file name will be dynamic (such as: QRSIS_19-Jan-19_09_30, QRSIS_20-jan-19_09_45,etc)
    3.Do you know the tab (sheet) name or search for the word QRSIS in all sheets? - there will be only 1 tab (sheet) in this files which also having similar names as files names.
    4.Copy the full sheet and overwrite the master sheet? - correct, copy the full sheet (column A to Z) and overwrite the master sheet (active sheet)
    a. Copy values, or all - copy all data from column a to z, rows are unlimited.

    hope its clear enough :)

    Good Morning,

    I have to collate data in daily spreadsheets. I have tried using formulas but the data changes daily so formulas do not work. From a master file I wanted to create a macro that would give a list specific files in user folder (download folder) and select the correct file and then go to a tab in that file with content specific character called "QRSIS", copy this tab return to the master file and paste into master tab by replacing any existing data. I have tried to amend macros from several sources but do not know enough about VBA to get it to do what i want , :((. Any kind help would be great.

    Note: master file is a common/public file, different user will use but the file folder will be the same, its under download folder.

    appreciate your help


    Hi Carim,
    Sorry I'm away for couple days,
    i tried with the code its perfectly fit, but the content become way to small to read also some text are become missing from columns. i'm printing from Columns A:G with unlimited rows.

    still looking best way to fit my content into one page and readable. :D

    Pleased to hear this could help you out ...:smile:

    Hi Again Carim,

    Sorry for out of topic, i'm so newbie here,
    how to adjust autofit for the print area?i tried this code :


    Worksheets("Master Table").Columns("$A$1").AutoFit

    but it always gave me error, basically what i wanted is all the columns in Master Table fit into 1 page, cause the Master Table will distribute as PDF file.

    Thank you,


    thanks for advice, its work very well now :D.
    sorry for out of topic, but where i can find color code for VBA?


    hello, its me again..
    can someone advice me how to change font size and type for header with VBA ?
    currently i used below code for header :


    ActiveSheet.PageSetup.CenterHeader = Chr(10) & Format(Int(Now()) - 1, "DD/MMMM/YYYY HH:MM") & " - " & Format(Now(), "DD/MMMM/YYYY HH:MM")

    appreciate any kind of advice :D

    hi, please find attached sample of the file,maybe its better to try so you will get the idea,
    the main idea is if i tick the break active box (C3) , D3 cell will automatically choose break, if not tick than D3 will choose whatever value user choose.and F3 will always gave timestamp what ever you choose in D3.

    thank you :D

    hallo :D


    i'm sorry i think i miss type about cell refference, here the correctuion D3 cells has no formula, its only drop down list and F3 have this formula: =IF(D3<>"",IF(AND(F3<>"",CELL("address")=ADDRESS(ROW(D3),COLUMN(D3))),NOW(),IF(CELL("address")<>ADDRESS(ROW(D3),COLUMN(D3)),F3,NOW())),"")"
    also, G3 cells dependent with this formula : =IF(D3="break",TEXT(NOW()-F3,"h:mm"),"")


    1. When the check box is ticked, what should cell D3 give? Are you expecting the code to insert a value into cell D3 which overwrites the formula - when box ticked D3 will give one of option which is in drop down list,lets say the value is break and it should not overwrite the drop down list. if box not thick than D3 are empty instead drop down list option are available.

    2. Similarly, for cell F3, the output for cell D3 will not provide a "break" string, so the F3 cell will always be blank? - the F3 is time stamp cells, whatever changed in D3 it will update the time stamp,it blank if only D3 cell also blank.

    i will attached sample file soon.

    Hello everyone,

    i'm sorry if this topic been discussed before, i tried search but couldn't find it.
    here is my situation,i would like to use check box (activeX control) to be used in excel sheet with VBA,if i tick the check box the cells D3 value will be change (but D3 cells have drop down list) also F3 cells will gave actual time stamp.
    in other situation, i have already formula in D3 and F3 cells which use for time stamp, here is the formula in quote :


    D3 formula : =IF(D3<>"",IF(AND(F3<>"",CELL("address")=ADDRESS(ROW(D3),COLUMN(D3))),NOW(),IF(CELL("address")<>ADDRESS(ROW(D3),COLUMN(D3)),F3,NOW())),"") . F3 Formula : =IF(D3="break",TEXT(NOW()-F3,"h:mm"),"")


    can somebody advice me the VBA for check box?

    very thanks in advance.