Wise Owl Answers - How do I draw a line in a cell in Excel VBA?

Подписаться 208 тыс.

Просмотров 2,6 тыс.

50% 1

By Andrew Gould
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can join this channel to get access to perks:
/ @wiseowltutorials
Or you can click this link www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!
This video shows how to use VBA to draw lines on an Excel worksheet and position them relative to cells. You'll learn how to set the X and Y co-ordinates of the line start and end, and how to reference the Left, Top, Width and Height properties of a cell to control the position of the line.
Chapters
00:00 The Question
00:34 Drawing a Line
02:59 Positioning a Line Relative to a Cell
04:37 Deleting all Shapes on a Worksheet
05:20 Drawing a Horizontal Line Halfway Down a Cell
06:32 Drawing a Line Across Multiple Cells
07:21 Changing How a Line Moves and Resizes
09:18 Drawing Multiple Lines at Intervals in a Cell
12:34 Adding Horizontal Lines
14:11 Dividing a Cell with Multiple Diagonal Lines
17:05 A Final Example
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Power BI, DAX, VBA, Python, Visual C#, Microsoft SQL Server, SQL Server Reporting Services SSRS, SQL Server Integration Services SSIS, Microsoft Access and more!

Опубликовано:

5 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 27

@wizardofaus8473 7 месяцев назад

Thank you Andrew. I absolutely love your tutorials.

@WiseOwlTutorials 7 месяцев назад

You're very welcome! Thanks for taking the time to comment and for watching!

@nadermounir8228 7 месяцев назад

Thank u Andrew. Great tutorial as always 👌

@WiseOwlTutorials 7 месяцев назад

Thank you as always for your kind comments!

@sharadgupta2007 2 года назад

Thanks for sharing great knowledge. You are such a great mentor.

@WiseOwlTutorials 2 года назад

Thank you! I'm happy to hear that you found it useful and thank you for your support!

@benlinford_work 7 месяцев назад

Always love to see an Excel VBA drop from WiseOwl. Always much value to be gleaned. Great demonstration of universal concepts with VBA as the vector. Merry Christmas and Happy New Year, Andrew. 🎄

@WiseOwlTutorials 7 месяцев назад

Thanks so much Ben! Merry Christmas and Happy New Year to you too!

@KhalilYasser 7 месяцев назад

Thank you very much for the amazing videos.

@WiseOwlTutorials 7 месяцев назад

You're very welcome as always Yasser, great to hear from you!

@elricho72 7 месяцев назад

Great Video ! Thanks

@WiseOwlTutorials 7 месяцев назад

Thanks for watching!

@sarikasingh4540 18 дней назад

How do I delete excel workbook on SharePoint folder?

@sandee4u 7 месяцев назад

hi, is there any way that the value of X & Y is referenced from cells value & instead of writing inside a code & the result may be as per the XY coordinates. The plotted graph, if with multiple lines may also be scaled as required by dragging.

@WiseOwlTutorials 7 месяцев назад

Hi! Yes of course, you can reference the value of a range in the usual way: Set sh = Sheet1.Shapes.AddLine( _ BeginX:=Range("A1").Value, _ BeginY:=Range("A2").Value, _ EndX:=Range("A3").Value, _ EndY:=Range("A4").Value) For scaling you have limited options. The part of the video which talks about Changing How a Line Moves and Resizes covers this. I hope it helps!

@danp6101 7 месяцев назад

Hi Andrew. Is there away to make a message box use the x and y coordinates to appear relative to where the workbook is located on the monitor. Lets say I want the message box to appear X =100 from the top of the worksheet and Y is the center of the Worksheet. Now if I have the Workbook is anywhere on the monitor. The Message Box will always be at the same location on the worksheet.. Thanks.

@WiseOwlTutorials 7 месяцев назад

Hi Dan! Annoyingly, there's no easy way to do this with a message box (even more annoying because you can do it with an inputbox!). If you want to set the position of a MsgBox you'll need to use some Windows API function calls: answers.microsoft.com/en-us/msoffice/forum/all/how-do-i-place-a-vba-msgbox-to-a-specific-location/8c965cd8-7ef3-4b39-aec6-1ecc10cf8f39 www.mrexcel.com/board/threads/vba-position-a-message-box.19768/ It's slightly easier to set the position of a custom user form, so you could create a custom form which looks like a MsgBox and display this at a specific location on the screen using its Top and Left properties. Annoyingly, the Top and Left of a userform is set relative to the screen, rather than the application, so you may need to call some Windows API functions anyway to get it in exactly the position you want! stackoverflow.com/questions/41884148/how-do-i-align-a-userform-next-to-the-active-cell

@danp6101 7 месяцев назад

@@WiseOwlTutorials Thanks Andrew for the response.

@WiseOwlTutorials 7 месяцев назад

@@danp6101 No probs hope you find a solution!

@brp7016 7 месяцев назад

Is there a way where we can build on it and for example highlight 4 cells and have it make an arrow along side the cells in a downward position?

@WiseOwlTutorials 7 месяцев назад

I mean, sure! You can reference the Selection object to pick up the selected range: Sub Draw_Lines() Dim sh As Shape Set sh = Sheet1.Shapes.AddLine( _ BeginX:=Selection.Left, _ BeginY:=Selection.Top, _ EndX:=Selection.Left, _ EndY:=Selection.Top + Selection.Height) sh.Line.ForeColor.RGB = rgbHotPink sh.Line.EndArrowheadStyle = msoArrowheadTriangle End Sub

@brp7016 7 месяцев назад

@@WiseOwlTutorials thank you!

@WiseOwlTutorials 7 месяцев назад

@@brp7016 No problem!

@kiendra 7 месяцев назад

Hey andrew, how do i make a button in excel vba that opens a user form?

@WiseOwlTutorials 7 месяцев назад

Hi! I think you'll find part 2 of this playlist useful ru-vid.com/group/PLNIs-AWhQzckOzn3l2_VWUicXZvywNSI4&feature=shared Or part 21 of this playlist ru-vid.com/group/PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5&feature=shared

@kiendra 7 месяцев назад

@@WiseOwlTutorialsthanks!

@WiseOwlTutorials 7 месяцев назад

@@kiendra No problem!