ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Error in code (https://www.excelbanter.com/excel-programming/381169-error-code.html)

Corey
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....



NickHK
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....

Corey
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....

NickHK
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....

NickHK
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....

Corey
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....

NickHK
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....

Corey
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....

NickHK
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....


All times are GMT +1. The time now is 01:54 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com