Macro doesn't work in Excel 97

A

anthony

I have complete a macro in excel 2000, which works
perfectly, however when I try to run the macro on another
computer that running excel 97, I keep getting script
errors and the debug screen appears.

Does anyone have any suggestions what I can change or do
to solve the problem.

The macro is as follows:
Dim EntryCount As Single
Dim empNO As String
Dim empName As String
Dim empSurName As String
Dim empLoc As String
Private Sub CommandButton2_Click()
'Ensure ListBox contains list items
If ListBox1.ListCount >= 1 Then
'If no selection, choose last list item.
If ListBox1.ListIndex = -1 Then
ListBox1.ListIndex = _
ListBox1.ListCount - 1
End If
ListBox1.RemoveItem (ListBox1.ListIndex)
End If
End Sub
Private Sub UserForm_Initialize()
EntryCount = 0
CommandButton1.Caption = "Add Item"
CommandButton2.Caption = "Remove Item"
End Sub

Private Sub cmdDo_Click()
Macro2
End Sub


Private Sub ComboBox2_Change()
For l = 2 To recordno
empNO = Worksheets("sheet1").Range("A" & l)
empName = Worksheets("sheet1").Range("C" & l)
empSurName = Worksheets("sheet1").Range("B" & l)
ComboBox1.AddItem (empNO & "-" & empName & " " &
empSurName)
Next l
End Sub

Sub cmdDo2_Click()
Dim Lastrow As Long

unprotect ("cac")

Range("B800").Select
Selection.End(xlUp).Select
Lastrow = Selection.Row

Range(Cells(6, 5), Cells(Lastrow, 20)).Select

Selection.ClearContents
Range("E6").Select
protect ("cac")


End Sub

Sub CommandButton1_Click()
Delete_it
End Sub


Sub Employee_Click()
New_Emp
End Sub
Sub Print_it_Click()
Dim r As Long

r = Range("e1000").End(xlUp).Row

Range(Cells(1, 1), Cells(r, 18)).PrintPreview

'ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
End Sub



'Help Im not sure how to complete the rest of the code
so that it limited to a select range

Private Sub Worksheet_Change(ByVal Target As Range)
Dim str As String

If Target.Row >= 6 Then
If Target.Column >= 10 And Target.Column <= 14
Then
str = InputBox("Please enter a date(s).
Example 05-09/07 (dd/mm)", "Date")
If str = "" Then
Exit Sub
End If
str = "(" & Cells(5, Target.Column) & " - " &
str & ")"
Cells(Target.Row, 17).Value = Cells
(Target.Row, 17).Value & str

End If
End If
End Sub

Sub test()
Dim Answer As Integer
Dim Answer2 As Integer
MsgBox "This is just information"

Answer = MsgBox("Enter Info", vbYesNo, "Title here")
If Answer = vbYes Then
'whatever
Else
'user clicked No

End If

End Sub
 
D

Dave Peterson

Is this code behind a worksheet and invoked by clicking on a button from the
control toolbox toolbar?

If yes, then add

activecell.activate

at the top of the code.

Or you can change the .Takefocusonclick to false for that commandbutton (or any
control that has that property).
 
G

Guest

The codes are onvoked by clicking a command button within
the spreadsheet.

Will this still work using the activecell.active
 
D

Dave Peterson

activecell.activate

What happened when you tried it???

(I bet it did (or will) work ok.)

I like the ".takefocusonclick = false" better.

Just because the activecell.activate looks like it isn't doing anything. And
maybe someone will delete it or comment it out without knowing why it was there.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top