looking for some advice ...
looking for some advice ...
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 Mr.Keneth, thank you for your email,
i have look the link but so far no progress, i guess i am bit lost at the moment. I will try again.
will keep follow up in this page.
Unfortunately, with my old Windows XP machine ... I am not in a position to test your macro ...
For sure another contributor will give you a hand ...
Many thanks Carim for your effort,
will patiently wait for other contributor to advice...
In order to declare API functions in 64 Bit platform :
Hope this will help
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):
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
your code is perfectly working, its help me a lot
thank you for your kind help.
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.
1. What specific files, xls, xlsx, xlsm, etc.?
a. One or more?
2. I guess they will know what "correct" file means.
3. Do you know the tab (sheet) name or search for the word QRSIS in all sheets?
4. Copy the full sheet and overwrite the master sheet?
a. Copy values, or all?
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
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
hope its ok to keep posting and ask in this forum,cause i'm still learning VBA.
i wanted to have vba which allow me to save the file which in other sheet (lets pretend sheets 3) as JPG/PNG and send it with user outlook email. i've been trying several easy vba code but its doesn't work :(( .
Glad to hear macro operates properly ...:wink:
As far as aesthetic problems are concerned ... it is usually a long trial and error process ...:smile:
until now i've been doing trial and error anyway
Difficult to guess what you are actually doing ...
A couple of remarks ...
1. Print instructions are using the print range and you need adjust the Page Set up
2. Are you actually printing only one column ...???
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.
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 :
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.
Despite the fact you could do it manually ... once for ever ...
You could test following:
Hope this will help
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
D3: A drop down list which user will choose from:
If user chooses the "break" value from the list, it will display the timestamp value. If not, it will display whatever value user chose?
F3: Timestamp value whenever the checkbox is ticked?
Sorry, im still a abit confused by the operation
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.
Can i just ask:
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
"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())),"")"
2) Similarly, for cell F3, the output for cell D3 will not provide a "break" string, so the F3 cell will always be blank?
Maybe it will be helpful if you provide a test file for us to understand what you are trying to acheive :)?
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.
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.