Create Drop Down List to Display Shapes in Excel
How to create drop down list using data validation to display shapes in Excel with a macro. More details available here: www.exceltrain...
Here's the complete macro code:
Sub CreateDropDownListWithShapes()
' CreateDropDownListWithSDhapes Macro
' How To Create Drop Down List With Shapes In Excel
DeletePicture
Range("F2").Clear
Range("F2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A$2:$A$4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("F2") = "Circle"
ActiveSheet.Shapes.Range(Array("Oval 9")).Select
Selection.Copy
Range("G2").Select
ActiveSheet.Pictures.Paste.Select
Selection.ShapeRange.IncrementLeft 8.25
Selection.ShapeRange.IncrementTop 9.75
Selection.Formula = "=MyShapes"
Selection.ShapeRange.ScaleWidth 1.208488429, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 1.208488429, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.IncrementLeft -6.75
Selection.ShapeRange.IncrementTop -7.5
Range("F2").Select
End Sub
Sub DeletePicture()
Dim myObj
Dim Pictur
Set myObj = ActiveSheet.DrawingObjects
For Each Pictur In myObj
If Left(Pictur.Name, 6) = "Pictur" Then
Pictur.Select
Pictur.Delete
End If
Next
End Sub
Пікірлер: 14
Want to get more details: www.exceltrainingvideos.com/create-drop-down-list-to-display-shapes-in-excel/
Very very intersting your classes sir. Thank you so much sir.
@Exceltrainingvideos
2 жыл бұрын
Always welcome
Excellent article Sir. I had tried and had not been able to make it work on Office 2010. I tried with Office 2019 and watching the lesson again. I'll use this method in a job I'm doing. Thank you for sharing your knowledge. Greetings from Brazil.
Thanks
This is very interesting as always. Plz Sir, I am looking for application with fingerprint for attendance with excel VBA. Have you made sething like?? Plz share the link. Thanks indeed
Sir, this is really useful
@Exceltrainingvideos
2 жыл бұрын
Glad to hear that
Hi, can you provide detail for the creating the name range formula using index?
Thank you! What if I want to select the same name or shape again and again in another cell? For example, I need to select the same month again and again. And each month has a shape Because every time I select the name & shape disappears from the list.
My question is to calculate the employee’s age by simply writing his date of birth, i.e. writing the date of birth in TextBox 1, and the age appears automatically in TextBox 2, knowing that it is accepted to write the date in the format 01/01/1990 and 1990 only
@Exceltrainingvideos
2 жыл бұрын
This Excel tutorial will guide: www.exceltrainingvideos.com/subtract-dates-in-excel/
मेरा प्रश्न कर्मचारी की आयु की गणना केवल उसकी जन्मतिथि लिखकर, यानी टेक्स्टबॉक्स 1 में जन्म तिथि लिखकर करना है, और उम्र टेक्स्टबॉक्स 2 में स्वचालित रूप से दिखाई देती है, यह जानते हुए कि दिनांक 01/01 प्रारूप में लिखना स्वीकार किया जाता है। /1990 और 1990 केवल
@Exceltrainingvideos
2 жыл бұрын
www.exceltrainingvideos.com/subtract-dates-in-excel/