TheSwamp

Code Red => AutoLISP (Vanilla / Visual) => Topic started by: Coder on November 28, 2021, 11:14:56 AM

Title: Border Excel cells with outside style
Post by: Coder on November 28, 2021, 11:14:56 AM

Hello guys,

I am trying to border Excel cells from B2 to B5 with one outside border but my code does not work for outside border.

Code: [Select]

        (setq rang (vlax-get-property Sheet "Range" "B2:B5"))
        (setq bord (vlax-get-property rang "Borders"))
        (vlax-put-property bord "Color" (vlax-make-variant -1 3))
Anyone can help ?

Thank you.

Title: Re: Border Excel cells with outside style
Post by: It's Alive! on November 28, 2021, 05:03:16 PM

maybe you need to specify what edge?
https://docs.microsoft.com/en-us/office/vba/api/excel.xlbordersindex

Title: Re: Border Excel cells with outside style
Post by: Coder on November 29, 2021, 05:14:01 AM

Yes I searched the same link you shared but unfortunately I don't know how to use it with the codes.
Can you please shed some light on it?
Thank you.

Title: Re: Border Excel cells with outside style
Post by: HOSNEYALAA on November 29, 2021, 06:48:46 AM

HI
TRY

Code: [Select]




 (setq rang (vlax-get-property Sht "Range" "B2"))


(setq Cels (vlax-get-property Rang "Cells"))
(setq R1 1
C1 1)

(setq Cel (vlax-variant-value
(vlax-get-property Cels "Item"
;; row number :
(vlax-make-variant R1)
;; column number :
(vlax-make-variant C1))))


(setq Bords (vlax-get-property Cel "Borders"))

(setq cnt 0)


(vlax-for a Bords
  (setq cnt (1+ cnt))
  (vl-catch-all-apply (function (lambda()
  (progn
    (if (OR (= cnt 3)(= cnt 2)(= cnt 1));;(< cnt 4)
      (progn
(vlax-put-property a "LineStyle"(vlax-make-variant 1 3))
(vlax-put-property a "Weight"(vlax-make-variant 4 3))
(vlax-put-property a "ColorIndex"(vlax-make-variant 1 5))
);progn
      ;; turn off the diagonal lines:
      (vlax-put-property a "LineStyle" (vlax-make-variant -4142 3))
      )
    )
  )
)
    )
  )









 (setq rang (vlax-get-property Sht "Range" "B3"))


(setq Cels (vlax-get-property Rang "Cells"))
(setq R1 1
C1 1)

(setq Cel (vlax-variant-value
(vlax-get-property Cels "Item"
;; row number :
(vlax-make-variant R1)
;; column number :
(vlax-make-variant C1))))


(setq Bords (vlax-get-property Cel "Borders"))

(setq cnt 0)


(vlax-for a Bords
  (setq cnt (1+ cnt))
  (vl-catch-all-apply (function (lambda()
  (progn
    (if (OR (= cnt 2)(= cnt 1));;(< cnt 4)
      (progn
(vlax-put-property a "LineStyle"(vlax-make-variant 1 3))
(vlax-put-property a "Weight"(vlax-make-variant 4 3))
(vlax-put-property a "ColorIndex"(vlax-make-variant 1 5))
);progn
      ;; turn off the diagonal lines:
      (vlax-put-property a "LineStyle" (vlax-make-variant -4142 3))
      )
    )
  )
)
    )
  )








 (setq rang (vlax-get-property Sht "Range" "B4"))


(setq Cels (vlax-get-property Rang "Cells"))
(setq R1 1
C1 1)

(setq Cel (vlax-variant-value
(vlax-get-property Cels "Item"
;; row number :
(vlax-make-variant R1)
;; column number :
(vlax-make-variant C1))))


(setq Bords (vlax-get-property Cel "Borders"))

(setq cnt 0)


(vlax-for a Bords
  (setq cnt (1+ cnt))
  (vl-catch-all-apply (function (lambda()
  (progn
    (if (OR(= cnt 2)(= cnt 1));;(< cnt 4)
      (progn
(vlax-put-property a "LineStyle"(vlax-make-variant 1 3))
(vlax-put-property a "Weight"(vlax-make-variant 4 3))
(vlax-put-property a "ColorIndex"(vlax-make-variant 1 5))
);progn
      ;; turn off the diagonal lines:
      (vlax-put-property a "LineStyle" (vlax-make-variant -4142 3))
      )
    )
  )
)
    )
  )














 (setq rang (vlax-get-property Sht "Range" "B5"))


(setq Cels (vlax-get-property Rang "Cells"))
(setq R1 1
C1 1)

(setq Cel (vlax-variant-value
(vlax-get-property Cels "Item"
;; row number :
(vlax-make-variant R1)
;; column number :
(vlax-make-variant C1))))


(setq Bords (vlax-get-property Cel "Borders"))

(setq cnt 0)


(vlax-for a Bords
  (setq cnt (1+ cnt))
  (vl-catch-all-apply (function (lambda()
  (progn
    (if (OR (= cnt 4)(= cnt 2)(= cnt 1));;(< cnt 4)
      (progn
(vlax-put-property a "LineStyle"(vlax-make-variant 1 3))
(vlax-put-property a "Weight"(vlax-make-variant 4 3))
(vlax-put-property a "ColorIndex"(vlax-make-variant 1 5))
);progn
      ;; turn off the diagonal lines:
      (vlax-put-property a "LineStyle" (vlax-make-variant -4142 3))
      )
    )
  )
)
    )
  )











Title: Re: Border Excel cells with outside style
Post by: Coder on November 29, 2021, 08:35:17 AM

Thank you hosneyalaa, it works great.