Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't work in Excel 97
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't work in Excel 97
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). anthony wrote: 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't work in Excel 97
The codes are onvoked by clicking a command button within
the spreadsheet. Will this still work using the activecell.active -----Original Message----- 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). anthony wrote: 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 -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to update a column in a work based on another work sheet | New Users to Excel | |||
Macro doesn't work in Excel 2003 (Con't) | Excel Programming | |||
Macro doesn't work in Excel 2003 | Excel Programming | |||
Macro: Excel 2000 does not work, XP does... | Excel Programming | |||
Solver won't work in macro, Excel 2002 SP1 | Excel Programming |