vba coding error in excel?

Question

Want a dropdown list that I can use to call different macros depending on choice made. So I was trying to use an if condition based on the field text. I added this in the Dropdown code (Dropdown1 is the automatically given name by VBA).


Sub DropDown1_Change()

Dim temp As Date
Dim newday As Integer
Dim newmonth As Integer
Dim newyear As Integer
Dim temp2 As String
temp2 = DropDown1.Text
Range("F5").Select
temp = DateValue(ActiveCell)


If temp2 = "1 Week" Then
MsgBox DropDown4.Text
End If

newday = Day(temp) + 7
newmonth = Month(temp)
newyear = Year(temp)
ActiveCell.Value = DateSerial(newyear, newmonth, newday)

End Sub

The code isn't finished. Don't worry about the bottom. It fails every time at "temp2 = DropDown1.Text". Says "Run-Time Error 424: Object Required". What's the problem?

Also, how can I see the code that VBA automatically creates for the dropdown (if possible)? I thought that maybe if I could see it, it would lead me to the answer. Tried every button in the developer tab. Couldn't find it.

Thanks....

Answer 1

Accessing a forms control is different from accessing an activex control. In an activex control you'd do as you're doing:

temp2=DropDown1.Text



Edit:

Forms controls are more annoying when you try to access their properties directly:

Dim ws as worksheet

Dim dd as DropDown

Set ws = ActiveSheet

Set dd = ws.Shapes("Drop Down 1").OLEFormat.Object

temp2 = dd.List(dd.value)



I've found it easier to deal with the cell link values/input range values (right click control>select control tab) and use offset formulas to extract the values of a forms dropdown. E.g. if the cell link property is A1 and the input range = A2:A10 then your formula becomes:

temp2 = Range("A1").Offset(Range("A1").Value, 0).Value



More simply, you could use a select case, where the values correspond to the linked cell value:

Select Case Range("A1").Value

Case 1 '1 Week

'do stuff

Case 2

'do stuff

Case 3

End Select

Answer 2

The reason why you're getting an Error is because the Target is a Range. That range can be 1 cell or multiple cells. When you delete a row Targe is the cells for that entire row. The Formula property applies to one individual cell. So you were trying to apply a property that can only be used on one individual cell on a group of cells. So Excel says "Wait a minute! You can't do that!" As they said, you either have to limit the change to 1 cell or go through each cell. I recommend going through each in case they paste data. I did notice one other MAJOR problem you had with your code. That is the InStr function. You need to start learning to use Debug.Print to return the values from your variables when you are debugging your code. If you had, you'd have noticed that InStr returns a number and not a True or False like Boolean values do. That means you can't really use it in your if statement. Instead you need to use the Like Operator like I did below. InStr returns the position of the first instance of one string inside another. Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) Dim rng As Range Application.ScreenUpdating = False For Each rng In Target If UCase(rng.Formula) Like "=*SHEET2!*" Then rng.Value = "" End If Next rng Application.ScreenUpdating = True End Sub This coding makes sure it only deletes the data when it is a formula that starts with an equals sign and references Sheet2.

Answer 3

It seems that you need a listbox instead of a dropdown box.



. .