| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in code
I am getting anerror with the below code:
Is there a way i can get it to use the ActiveCell that triggered the macro here ?? Private Sub ComboBox1_DropButtonClick() Application.ScreenUpdating = False If ComboBox1.ListCount 0 Then Exit Sub 'Place the References in here for the Roll Numbers and Lengths Dim lastcell As Long Dim myrow As Long On Error Resume Next lastcell = workSheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.workSheets("InspectionData") For myrow = 2 To lastcell If .Cells(myrow, 1) < "" Then If .Cells(myrow, 1).Offset(-1, 2).Text = Sheet5.Range("B2").Value And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then For i = 2 To 22 If Cells(myrow, 3).Offset(i, 0).Value < "" Then If Sheet5.ActiveCell = .Cells(myrow, 1).Offset(0, 0).Value Then '<=========== Error with ActiveCell ComboBox1.AddItem Cells(myrow, 3).Offset(i, 0) ComboBox1.List(ComboBox1.ListCount - 1, 1) = Cells(myrow, 3).Offset(i, 0).Address End If Next i End If End If Next End With Application.ScreenUpdating = True End Sub Corey.... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error in code
Corey,
Maybe you need to change the TakeFocusOnClick of the combo box from True to false. NickHK "Corey" wrote in message ... I am getting anerror with the below code: Is there a way i can get it to use the ActiveCell that triggered the macro here ?? Private Sub ComboBox1_DropButtonClick() Application.ScreenUpdating = False If ComboBox1.ListCount 0 Then Exit Sub 'Place the References in here for the Roll Numbers and Lengths Dim lastcell As Long Dim myrow As Long On Error Resume Next lastcell = workSheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.workSheets("InspectionData") For myrow = 2 To lastcell If .Cells(myrow, 1) < "" Then If .Cells(myrow, 1).Offset(-1, 2).Text = Sheet5.Range("B2").Value And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then For i = 2 To 22 If Cells(myrow, 3).Offset(i, 0).Value < "" Then If Sheet5.ActiveCell = .Cells(myrow, 1).Offset(0, 0).Value Then '<=========== Error with ActiveCell ComboBox1.AddItem Cells(myrow, 3).Offset(i, 0) ComboBox1.List(ComboBox1.ListCount - 1, 1) = Cells(myrow, 3).Offset(i, 0).Address End If Next i End If End If Next End With Application.ScreenUpdating = True End Sub Corey.... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error in code
Nick,
Will that stop the error in that line then ? "NickHK" wrote in message ... Corey, Maybe you need to change the TakeFocusOnClick of the combo box from True to false. NickHK "Corey" wrote in message ... I am getting anerror with the below code: Is there a way i can get it to use the ActiveCell that triggered the macro here ?? Private Sub ComboBox1_DropButtonClick() Application.ScreenUpdating = False If ComboBox1.ListCount 0 Then Exit Sub 'Place the References in here for the Roll Numbers and Lengths Dim lastcell As Long Dim myrow As Long On Error Resume Next lastcell = workSheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.workSheets("InspectionData") For myrow = 2 To lastcell If .Cells(myrow, 1) < "" Then If .Cells(myrow, 1).Offset(-1, 2).Text = Sheet5.Range("B2").Value And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then For i = 2 To 22 If Cells(myrow, 3).Offset(i, 0).Value < "" Then If Sheet5.ActiveCell = .Cells(myrow, 1).Offset(0, 0).Value Then '<=========== Error with ActiveCell ComboBox1.AddItem Cells(myrow, 3).Offset(i, 0) ComboBox1.List(ComboBox1.ListCount - 1, 1) = Cells(myrow, 3).Offset(i, 0).Address End If Next i End If End If Next End With Application.ScreenUpdating = True End Sub Corey.... |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error in code
Corey,
Err,.. no, because there is no TakeFocusOnClick property for a combo box. I was thinking of the command button. NickHK "Corey" wrote in message ... Nick, Will that stop the error in that line then ? "NickHK" wrote in message ... Corey, Maybe you need to change the TakeFocusOnClick of the combo box from True to false. NickHK "Corey" wrote in message ... I am getting anerror with the below code: Is there a way i can get it to use the ActiveCell that triggered the macro here ?? Private Sub ComboBox1_DropButtonClick() Application.ScreenUpdating = False If ComboBox1.ListCount 0 Then Exit Sub 'Place the References in here for the Roll Numbers and Lengths Dim lastcell As Long Dim myrow As Long On Error Resume Next lastcell = workSheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.workSheets("InspectionData") For myrow = 2 To lastcell If .Cells(myrow, 1) < "" Then If .Cells(myrow, 1).Offset(-1, 2).Text = Sheet5.Range("B2").Value And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then For i = 2 To 22 If Cells(myrow, 3).Offset(i, 0).Value < "" Then If Sheet5.ActiveCell = .Cells(myrow, 1).Offset(0, 0).Value Then '<=========== Error with ActiveCell ComboBox1.AddItem Cells(myrow, 3).Offset(i, 0) ComboBox1.List(ComboBox1.ListCount - 1, 1) = Cells(myrow, 3).Offset(i, 0).Address End If Next i End If End If Next End With Application.ScreenUpdating = True End Sub Corey.... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error in code
Corey,
Activecell is not a property of a specific sheet. There is only ever 1 ActiveCell, on the active sheet. Depends what you are doing, but maybe If ActiveCell = .Cells(myrow, 1).Offset(0, 0).Value Then NickHK "Corey" wrote in message ... I am getting anerror with the below code: Is there a way i can get it to use the ActiveCell that triggered the macro here ?? Private Sub ComboBox1_DropButtonClick() Application.ScreenUpdating = False If ComboBox1.ListCount 0 Then Exit Sub 'Place the References in here for the Roll Numbers and Lengths Dim lastcell As Long Dim myrow As Long On Error Resume Next lastcell = workSheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.workSheets("InspectionData") For myrow = 2 To lastcell If .Cells(myrow, 1) < "" Then If .Cells(myrow, 1).Offset(-1, 2).Text = Sheet5.Range("B2").Value And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then For i = 2 To 22 If Cells(myrow, 3).Offset(i, 0).Value < "" Then If Sheet5.ActiveCell = .Cells(myrow, 1).Offset(0, 0).Value Then '<=========== Error with ActiveCell ComboBox1.AddItem Cells(myrow, 3).Offset(i, 0) ComboBox1.List(ComboBox1.ListCount - 1, 1) = Cells(myrow, 3).Offset(i, 0).Address End If Next i End If End If Next End With Application.ScreenUpdating = True End Sub Corey.... |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error in code
Thanks Nick,
I will give that a go tomorrow. When i am refering to the Activecell i mean the Cell that Triggered the Macro(Range Selecetion Change event) to run. Corey.... "NickHK" wrote in message ... Corey, Activecell is not a property of a specific sheet. There is only ever 1 ActiveCell, on the active sheet. Depends what you are doing, but maybe If ActiveCell = .Cells(myrow, 1).Offset(0, 0).Value Then NickHK "Corey" wrote in message ... I am getting anerror with the below code: Is there a way i can get it to use the ActiveCell that triggered the macro here ?? Private Sub ComboBox1_DropButtonClick() Application.ScreenUpdating = False If ComboBox1.ListCount 0 Then Exit Sub 'Place the References in here for the Roll Numbers and Lengths Dim lastcell As Long Dim myrow As Long On Error Resume Next lastcell = workSheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.workSheets("InspectionData") For myrow = 2 To lastcell If .Cells(myrow, 1) < "" Then If .Cells(myrow, 1).Offset(-1, 2).Text = Sheet5.Range("B2").Value And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then For i = 2 To 22 If Cells(myrow, 3).Offset(i, 0).Value < "" Then If Sheet5.ActiveCell = .Cells(myrow, 1).Offset(0, 0).Value Then '<=========== Error with ActiveCell ComboBox1.AddItem Cells(myrow, 3).Offset(i, 0) ComboBox1.List(ComboBox1.ListCount - 1, 1) = Cells(myrow, 3).Offset(i, 0).Address End If Next i End If End If Next End With Application.ScreenUpdating = True End Sub Corey.... |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error in code
Corey,
But this code is in the ComboBox1_DropButtonClick() event, so you can use the ActiveCell. ActiveCell.Parent tells you the WS that is active, as does ActiveSheet. If you do mean to use the Worksheet_SelectionChange event, then the argument "(ByVal Target As Range)" is passed, which tells you the range that triggered this event. NickHK "Corey" wrote in message ... Thanks Nick, I will give that a go tomorrow. When i am refering to the Activecell i mean the Cell that Triggered the Macro(Range Selecetion Change event) to run. Corey.... "NickHK" wrote in message ... Corey, Activecell is not a property of a specific sheet. There is only ever 1 ActiveCell, on the active sheet. Depends what you are doing, but maybe If ActiveCell = .Cells(myrow, 1).Offset(0, 0).Value Then NickHK "Corey" wrote in message ... I am getting anerror with the below code: Is there a way i can get it to use the ActiveCell that triggered the macro here ?? Private Sub ComboBox1_DropButtonClick() Application.ScreenUpdating = False If ComboBox1.ListCount 0 Then Exit Sub 'Place the References in here for the Roll Numbers and Lengths Dim lastcell As Long Dim myrow As Long On Error Resume Next lastcell = workSheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.workSheets("InspectionData") For myrow = 2 To lastcell If .Cells(myrow, 1) < "" Then If .Cells(myrow, 1).Offset(-1, 2).Text = Sheet5.Range("B2").Value And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then For i = 2 To 22 If Cells(myrow, 3).Offset(i, 0).Value < "" Then If Sheet5.ActiveCell = .Cells(myrow, 1).Offset(0, 0).Value Then '<=========== Error with ActiveCell ComboBox1.AddItem Cells(myrow, 3).Offset(i, 0) ComboBox1.List(ComboBox1.ListCount - 1, 1) = Cells(myrow, 3).Offset(i, 0).Address End If Next i End If End If Next End With Application.ScreenUpdating = True End Sub Corey.... |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error in code
Nick,
When the user clicks on a cell(in sheet5) in the range i have a Userform popup to prompt for a value in the list. The cell that run the macro is the ActiveCell that i was refering to. I have a Value in Column A.(From sheet4.Column A) then Then a user selects a value in the SAME ROW in Column F(sheet5). The value in Column A (sheet5)refers to a value in Column A in another sheet., (sheet4) I then need the values in Column C(sheet4) that are Offset from the Column A(sheet4). If the value in Column A(sheet4) was in Cell A102, then, The Range or list of values that i need to populate in the ColumnF list(Userform Combobox) is: a Range of ("C104:C124") in (sheet4) Corey.... "NickHK" wrote in message ... Corey, But this code is in the ComboBox1_DropButtonClick() event, so you can use the ActiveCell. ActiveCell.Parent tells you the WS that is active, as does ActiveSheet. If you do mean to use the Worksheet_SelectionChange event, then the argument "(ByVal Target As Range)" is passed, which tells you the range that triggered this event. NickHK "Corey" wrote in message ... Thanks Nick, I will give that a go tomorrow. When i am refering to the Activecell i mean the Cell that Triggered the Macro(Range Selecetion Change event) to run. Corey.... "NickHK" wrote in message ... Corey, Activecell is not a property of a specific sheet. There is only ever 1 ActiveCell, on the active sheet. Depends what you are doing, but maybe If ActiveCell = .Cells(myrow, 1).Offset(0, 0).Value Then NickHK "Corey" wrote in message ... I am getting anerror with the below code: Is there a way i can get it to use the ActiveCell that triggered the macro here ?? Private Sub ComboBox1_DropButtonClick() Application.ScreenUpdating = False If ComboBox1.ListCount 0 Then Exit Sub 'Place the References in here for the Roll Numbers and Lengths Dim lastcell As Long Dim myrow As Long On Error Resume Next lastcell = workSheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.workSheets("InspectionData") For myrow = 2 To lastcell If .Cells(myrow, 1) < "" Then If .Cells(myrow, 1).Offset(-1, 2).Text = Sheet5.Range("B2").Value And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then For i = 2 To 22 If Cells(myrow, 3).Offset(i, 0).Value < "" Then If Sheet5.ActiveCell = .Cells(myrow, 1).Offset(0, 0).Value Then '<=========== Error with ActiveCell ComboBox1.AddItem Cells(myrow, 3).Offset(i, 0) ComboBox1.List(ComboBox1.ListCount - 1, 1) = Cells(myrow, 3).Offset(i, 0).Address End If Next i End If End If Next End With Application.ScreenUpdating = True End Sub Corey.... |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error in code
Corey,
Not sure I follow all that, but maybe... Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim RowNumber as long RowNumber=Target.Row will make it more clear as to which address you are referring to. NickHK "Corey" wrote in message ... Nick, When the user clicks on a cell(in sheet5) in the range i have a Userform popup to prompt for a value in the list. The cell that run the macro is the ActiveCell that i was refering to. I have a Value in Column A.(From sheet4.Column A) then Then a user selects a value in the SAME ROW in Column F(sheet5). The value in Column A (sheet5)refers to a value in Column A in another sheet., (sheet4) I then need the values in Column C(sheet4) that are Offset from the Column A(sheet4). If the value in Column A(sheet4) was in Cell A102, then, The Range or list of values that i need to populate in the ColumnF list(Userform Combobox) is: a Range of ("C104:C124") in (sheet4) Corey.... "NickHK" wrote in message ... Corey, But this code is in the ComboBox1_DropButtonClick() event, so you can use the ActiveCell. ActiveCell.Parent tells you the WS that is active, as does ActiveSheet. If you do mean to use the Worksheet_SelectionChange event, then the argument "(ByVal Target As Range)" is passed, which tells you the range that triggered this event. NickHK "Corey" wrote in message ... Thanks Nick, I will give that a go tomorrow. When i am refering to the Activecell i mean the Cell that Triggered the Macro(Range Selecetion Change event) to run. Corey.... "NickHK" wrote in message ... Corey, Activecell is not a property of a specific sheet. There is only ever 1 ActiveCell, on the active sheet. Depends what you are doing, but maybe If ActiveCell = .Cells(myrow, 1).Offset(0, 0).Value Then NickHK "Corey" wrote in message ... I am getting anerror with the below code: Is there a way i can get it to use the ActiveCell that triggered the macro here ?? Private Sub ComboBox1_DropButtonClick() Application.ScreenUpdating = False If ComboBox1.ListCount 0 Then Exit Sub 'Place the References in here for the Roll Numbers and Lengths Dim lastcell As Long Dim myrow As Long On Error Resume Next lastcell = workSheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.workSheets("InspectionData") For myrow = 2 To lastcell If .Cells(myrow, 1) < "" Then If .Cells(myrow, 1).Offset(-1, 2).Text = Sheet5.Range("B2").Value And IsNumeric(Trim(Mid(.Cells(myrow, 1), 2))) = True Then For i = 2 To 22 If Cells(myrow, 3).Offset(i, 0).Value < "" Then If Sheet5.ActiveCell = .Cells(myrow, 1).Offset(0, 0).Value Then '<=========== Error with ActiveCell ComboBox1.AddItem Cells(myrow, 3).Offset(i, 0) ComboBox1.List(ComboBox1.ListCount - 1, 1) = Cells(myrow, 3).Offset(i, 0).Address End If Next i End If End If Next End With Application.ScreenUpdating = True End Sub Corey.... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
| OnTime code error "can't execute code in break mode" | Excel Programming | |||
| Error in Excel VBA Code (Error 91) | Excel Programming | |||
| How can I still go to the error-code after a On Error Goto? | Excel Programming | |||
| Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming | |||
Hybrid Mode
