View original (French)

Excel Macro VB - Drawing Rectangles

Solved
ridertart -  
Bonjour,

I would like some help with a project on Excel...

I don't know how to draw a rectangle with VB in Excel, but I want to draw several rectangles based on a number for each rectangle (the height of the rectangle), with a fixed width. The drawing should be executed when I click on a button I created.

The rectangles should be one below the other.

I also want to be able to copy and paste this drawing into Word, but will it be editable as if I had made a simple drawing with lines, rectangles, and text in Word?

I would also like to draw lines and put text next to each rectangle...

Can someone help me with the steps to follow?

Private Sub CommandButton1_Click()

...
...
...
...Rectangle( ...)

End Sub

I have searched for documentation on the internet, but I can't find anything that is well explained... Sorry, I'm not an expert in VB :)

Thanks in advance.
Configuration: Windows XP Internet Explorer 7.0 Office XP

7 answers

  1. ridertart Author
    Thank you, it's working for the rectangle; I managed to draw them as I wanted.
    I agree with x1 and x2, but wouldn't y1 and y2 in

    ActiveSheet.Shapes.AddShape(msoShapeRectangle, x1, y1, x2, y2).Select

    be more of the length and width of the rectangle rather than the coordinates of the bottom-right corner? At least, that's what it seems to me...

    However, now I have 2 other questions:

    How can I color my rectangle and, even more challenging, how can I add a fill pattern to my rectangle?

    Also, how can I insert a text box on an Excel sheet and write text inside it...?

    Thank you very much; I'm progressing little by little :)
    1. eriiic Contributor
      Hello,

      He is right, it's the best way to learn and progress. Plus, it saves you from digging through the documentation because in your recorded macro, you will find the names of the objects that interest you, the properties, the methods.
      And if a term escapes you, hit F1 on it and you have all the details, links to the other property you've been searching for for 2 hours, etc.
      So don't be lazy, turn on the macro recorder, draw a rectangle, change its size, position, color, border (color, thickness, style), text, etc.
      Stop the recorder, look at the code, and you will have all your answers, even the ones you weren't asking...
      eric
      1. gbinforme Contributor
        Hello

        How to color my rectangle
        how to put a fill pattern for my rectangle?
        how to insert a text box

        HERE
        --

        Knowledge is the only substance that increases when shared. (Socrates)
        1. ridertart Author
          Yes, so?

          Thank you for your super constructive response.
      2. gbinforme Contributor
        Hello

        I'm sorry for insisting a bit, but to do what you wanted, which was quite specific, I doubt there are many people who can "just click on it to write my macro."

        Often, even when you know the code, you have to search for a typo, so it's better to let VBA go on with its speech, and then we fine-tune.
        --

        Knowledge is the only thing that increases when shared. (Socrates)
        2. julplemet
          Hello,

          You can use the command 

          ActiveSheet.Shapes.AddShape(msoShapeRectangle, x1, y1, x2, y2).Select


          where x1,y1 are the coordinates of the top left point
          and x2,y2 are the coordinates of the bottom right point

          (to be replaced by variables containing real numbers (for example: ActiveSheet.Shapes.AddShape(msoShapeRectangle, 135.75, 148.5, 204.75, 165.75).Select))
          1. gbinforme Contributor
            Hello

            You want to do a lot of things but you don't know how to code.

            You go to Tools / Macro / New Macro

            You record the actions you want to perform in VBA and from there you will have the starting point of your code to put in your button.

            Then if you don't understand certain elements, it will be easier to help you.
            --

            Knowledge is the only material that increases when shared. (Socrates)
            1. phil_232
              Bravo, finally someone says it.
          2. ridertart Author
            Thank you, it's true that you helped me progress on VB ...

            When I used to create a macro, I would make a button and click directly on it to write my macro ... I do the same in Delphi ... yet this is following a class I had from a teacher a while ago ...

            Thank you.