Hi



I need a little help with some VB Code. I did do a one year VB course 8 years ago, and have forgotten most of it.

I need help with getting this code to be properly formatted and the correct syntax added.

Basically the code will stop the user (me) from entering a number more than 50.

var : weeknumber = integar

if weeknumber > 50 then
weeknumber = 50

end if

I need to know where I add this code too.

Many thanks

Leroy

I've managed to work out the code need (I think)

BUT

I need to know how I attach it to a cell. I need the code to look at the cell and decide whether or not to display the message box.

I'm not sure if the "Range(E5)" is the correct way of doing this.

Sub Over50()
Dim WeekNumber As Integer
WeekNumber = Range(E5)
If WeekNumber > 50 Then
MsgBox "To Many Weeks. This Number Will remain 50"
End If

End Sub

I am new at programming so I can't help in terms of correcting your code but I can see one area of trouble. See below.

Sub Over50()
Dim WeekNumber As Integer
Dim WeekChanged As Interger

WeekNumber = Range(E5)
WeekChanged = 50

If WeekNumber > 50 Then 'Check if WeekNumber is > then 50
WeekNumber=WeekChanged 'Change Cell E5 to equal 50
MsgBox "Too Many Weeks. This Number Will remain 50" 'Show message box
End If
End Sub

You haven't included in your IF statement a means for WeekNumber to be changed to 50. If you do not include WeekNumber = WeekChanged all that will occur is WeekNumber will retain its state, will stay > 50 and your messagebox will show but no actual change will occur.

I don't know if WeekNumber=WeekChanged is the correct VB code or if the Integer WeekChanged is declared correctly.

I will keep working on this and will post if I get any results.

I did some more work and found a solution that works.

It runs a check on Cell E5, changes its value to 50 if it is greater then 50 and then shows a message box to the user stating a change was made.

Sub GreaterThenFifty() 'Name of macro
Dim WeekNumber As Double 'Use Double instead in case a half a week is used. Double allows decimal points and larger values to be entered then Integer
Dim WeekChange As Double 'Use Double instead in case a half a week is used. Double allows decimal points and larger values to be entered then Integer

WeekNumber = ActiveSheet.Range("E5").Value

If WeekNumber > 50 Then 'Check if WeekNumber is greater the 50
ActiveSheet.Range("E5").Value = 50 'Change Cell E5 to 50 if above statement is true
Msg = "Week Number is greater then 50. Changed to 50."
MsgBox Msg 'Inform the user of the change
End If

End Sub

If this solution helped you please click the scales and raise my reputation.

great. thanks. scales clicked