Preventing number over 100 and limiting combobox to list

[litestream]

I'd like to prevent people entering a number over 100 in cell C10 of my worksheet. I found the following from Erik on the boards but it wouldn't work - maybe I should put it somewhere different to the worksheet module?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Const limit = 100
Set rng = Range("C10")
If Target.Count > 1 Then Exit Sub
With Application
.EnableEvents = False
If Len(Target) > limit Then .Undo
.EnableEvents = True
End With
End Sub

Also, I have a combobox with the following code:

Private Sub Workbook_Open()
Dim i As Integer
With Sheets("Data")
   With .ComboBox1
       .AddItem "Standard"
       .AddItem "Euro"
   End With
   With .ComboBox2
       For i = 1 To 7
          .AddItem i
       Next
   End With
End With
End Sub

Is there a way to prevent people entering numbers other than 1 to 7?

 

[erik.van.geit]

Hi,

sheetcode
that code was to prevent the users from entering more than 100 characters in C10, hence LEN !
you need

If Target > limit Then .Undo

combobo
see properties: STYLE
you need fmStyleDropDownList

kind regards,
Erik

 

[litestream]

Awesome! thanks Erik!

 

[erik.van.geit]

you're WELCOME !!

checking again this one ...
you could use validation for all this (validate cell C10 and replacing comboboxes by another validated cell)