Saturday, May 08, 2010

How to Dynamically Change Userform's Control properties from Excel Sheet using Excel VBA

How to Draw Rectangle in VBA using Excel Data

This is an exclusive request from Phil. If you find it interesting, I am more glad. The idea is to draw/resize a rectangle in userform based on values from Excel sheet.

I am using a label control for rectangle. Let us add a label control to userform and name it as LabelRect

To the WorkSheet_Change event of the required sheet add the following event

Private Sub Worksheet_Change(ByVal Target As Range)

Dim oLbl As MSForms.Label

Set oLbl = UserForm1.LabelRect

If Target.Address = "$B$1" Or Target.Address = "$B$2" Then

    If IsNumeric(Range("B1").Value) = True Then oLbl.Height = Range("B1").Value
    If IsNumeric(Range("B2").Value) = True Then oLbl.Width = Range("B2").Value
    oLbl.BackColor = vbGreen
    UserForm1.Show (False)
End If

End Sub

The code will get executed when there is a change in Value in column B1 and B2. Hence the label in the userform will be adjusted accordingly as shown below:

No comments:

Post a Comment

Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.