Draw automatic borders using VBA

I have a file , in which every week i will update data. First,i will enter week number, next i will fill up some data on two columns (material no and part id). After that I press update button
i need to see "Outside borders" automatically drawn.
pls help me , how to do that?

Originally Posted by joh46k

I have a file , in which every week i will update data. First,i will enter week number, next i will fill up some data on two columns (material no and part id). After that I press update button
i need to see "Outside borders" automatically drawn.
pls help me , how to do that?

I don't understand your request ! but I try to write a code --> You can test this code ?

Run sub NHM ()

Please Login or Register  to view this content.

Post this in you worksheet module code and when you make a change to the sheet it will automatically update for you.

Please Login or Register  to view this content.

@japandav : i have some questions
1)In future ,if i want to increase the rows where do i need to make changes in the above code.
2) Why the template file working as i wanted but i copy the same code paste in to my original file is not working as i wanted.
Below i attached my excel original file sheet , pls check

Thank you

Where are the week numbers? The Original file is different to the sample, so the code obviously won't work. How do tell each week?

Bump no response

Hi, joh46k,

taking JapanDaveīs concept of worksheet change please try this code and see if you may work with it (it will have an effect in the row you change/enter data). Code goes behind the sheet wwhich you want to monitor:

Please Login or Register  to view this content.

For updating with a Button I would check the date in Column M to build a range for applying borders there.

HTH,
Holger

@hahobe: thank you for your reply man ! i tried ur code but it is not what i wanted .

if you see post #3 , japandave code: His template exactly achieve what i wanted but the problem is ,when i put that code inside my original ALL file(#6) it is not working as in the template that he attached in post #3.
Can you help me , on that ?

Hi, joh46k,

a slight update of the code:

Please Login or Register  to view this content.

As the file I was working on shows more than 600 KB I hope Iīve been working on your most current upload.

Ciao,
Holger

@Hahobe : Bro did you tried Dave's post #3 attachment file ? i want something like that.

I tried your file, it creating a border , only if I manually type the data from B to M column . If i tried to copy & paste some data on B to M column , it is not creating a border.

If you tried DAVE'S post#3 file ,you will notice that you can type and also you can copy and pate as value , it will Automatically create a nice border for you .

Hi, joh46k,

Originally Posted by Post#11

If i tried to copy & paste some data on B to M column , it is not creating a border.

Originally Posted by OP

First,i will enter week number

Code will work due to OP (besides that I donīt like areas in a table being left empty). I restricted the code to work for only one cell as entry with

Please Login or Register  to view this content.

Deactivating that line will definitely create a RTE when pasting.

What are you pasting (area in the sample)? Is it always equal to a week or not? When will you enter the week number?

i want something like that.

What about answering the questions I just asked for finding out how to rewrite the event?

Ciao,
Holger

OK, try this.

I fixed some other little problems that were nagging me as well. This will update the boarders as you enter data in the table as you update the week No. and the Quarters Column in the B column. If you want it to update from other columns let me know.

Please Login or Register  to view this content.

DAVE : Thanks so much bro ! for you help man it is working correctly and i find where is the problem ?

i have cells that built with formulas that's the reason when every time, i enter the data the border keep expanding until 12000 rows and more than that.
i just now tried to delete that formulas and tried again it works perfectly.

Do you have any suggestion/ideas on how to solve that problem?

Picture is attached below , pls check thank you

Repace the code with this,

You really should have posted an exact copy of the workbook in the first place. I am on my 8th beer right now and it is getting hard to think straight! LOL

Please Login or Register  to view this content.

Thank you so much Japan dave and HaHoBe ! Great .It is working .

Btw . The code works but i don't know how it's works #15 and #13 . It will be best, if you all write instruction or guide beside the code on how each line works .if not, you can choose important line . by using these symbol ' ' or # #

Once again , Thanks very much .

No worries.

JapanDave : your code works WELL ,only after i delete the blank cells with formula . so How To delete blank cells in that "ALL" sheet coloumns?

I have blank cells in Coloumn B and F to K