ExcelBanter - unable to get the activate property of the range class

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - unable to get the activate property of the range class (https://www.excelbanter.com/excel-programming/404166-unable-get-activate-property-range-class.html)

pswanie

unable to get the activate property of the range class

im trying to

================================================== ===
what code do i add to a commandbutton2 for the next?

they enter name and lastname in the textbox. when hitting commandbutton2 i
need that data from textbox1 in the next open cell sheet1 column c down. and
then command button disable. refresh either userform1 or listbox1
================================================== ===

i got this code and it works
================================================== ===
Option Explicit
Private Sub CommandButton1_Click()
Dim myStr As String
Dim iCtr As Long
Dim mySep As String

mySep = ", "
myStr = ""
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) = True Then
myStr = myStr & ", " & .List(iCtr)
End If
Next iCtr
End With

If myStr = "" Then
'nothing checked
Else
myStr = Mid(myStr, Len(mySep) + 1)
End If

Worksheets("inv 1st page").Range("d42").Value = myStr
Unload UserForm1
Sheets("DATA SHEET").Select
Range("A111").Select

End Sub

Private Sub UserForm_Initialize()
Dim myRng As Range
With Worksheets("inputpage")
Set myRng = .Range("c1", .Cells(.Rows.Count, "c").End(xlUp))
End With

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption
.List = myRng.Value
End With
End Sub

================================================== ===

now i want to add this and get the message popup that say

unable to get the activate property of the range class
================================================== ===

Private Sub CommandButton1_Click()
Dim i As String
Dim Lrow As Variant
Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1,
0).Activate
If TextBox1.Value < "" Then
ActiveCell.Value = TextBox1.Value
End If
CommandButton1.Enabled = False

Per Jessen[_2_]

unable to get the activate property of the range class

On 12 Jan., 18:36, pswanie wrote:
im trying to

================================================== ===
what code do i add to a commandbutton2 for the next?

they enter name and lastname in the textbox. when hitting commandbutton2 i
need that data from textbox1 in the next open cell sheet1 column c down. and
then command button disable. refresh either userform1 or listbox1
================================================== ===

i got this code and it works
================================================== ===
Option Explicit
Private Sub CommandButton1_Click()
* * Dim myStr As String
* * Dim iCtr As Long
* * Dim mySep As String

* * mySep = ", "
* * myStr = ""
* * With Me.ListBox1
* * * * For iCtr = 0 To .ListCount - 1
* * * * * * If .Selected(iCtr) = True Then
* * * * * * * * myStr = myStr & ", " & .List(iCtr)
* * * * * * End If
* * * * Next iCtr
* * End With

* * If myStr = "" Then
* * * * 'nothing checked
* * Else
* * * * myStr = Mid(myStr, Len(mySep) + 1)
* * End If

* * Worksheets("inv 1st page").Range("d42").Value = myStr
* * Unload UserForm1
* * Sheets("DATA SHEET").Select
* * * * Range("A111").Select

End Sub

Private Sub UserForm_Initialize()
* * Dim myRng As Range
* * With Worksheets("inputpage")
* * * * Set myRng = .Range("c1", .Cells(.Rows.Count, "c").End(xlUp))
* * End With

* * With Me.ListBox1
* * * * .MultiSelect = fmMultiSelectMulti
* * * * .ListStyle = fmListStyleOption
* * * * .List = myRng.Value
* * End With
End Sub

*================================================= ====

* * * * * * *now i want to add this and get the message popup that say

* * * * * * *unable to get the activate property of the range class
================================================== ===

Private Sub CommandButton1_Click()
Dim i As String
Dim Lrow As Variant
* * Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1,
0).Activate
* * If TextBox1.Value < "" Then
* * ActiveCell.Value = TextBox1.Value
* * End If
* * CommandButton1.Enabled = False

Hi

Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1, 0).Activate
If TextBox1.Value < "" Then ActiveCell.Value = Me.TextBox1.Value
Me.CommandButton1.Enabled = False
End Sub

or

Private Sub CommandButton1_Click()
Dim Lrow As String
Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1, 0).Address
If TextBox1.Value < "" Then Lrow.Value = Me.TextBox1.Value
Me.CommandButton1.Enabled = False
End Sub

Regards,

Per

pswanie

unable to get the activate property of the range class

still cant get it to work..

how and where in my code should i put it?

"Per Jessen" wrote:

On 12 Jan., 18:36, pswanie wrote:
im trying to

================================================== ===
what code do i add to a commandbutton2 for the next?

they enter name and lastname in the textbox. when hitting commandbutton2 i
need that data from textbox1 in the next open cell sheet1 column c down. and
then command button disable. refresh either userform1 or listbox1
================================================== ===

i got this code and it works
================================================== ===
Option Explicit
Private Sub CommandButton1_Click()
Dim myStr As String
Dim iCtr As Long
Dim mySep As String

mySep = ", "
myStr = ""
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) = True Then
myStr = myStr & ", " & .List(iCtr)
End If
Next iCtr
End With

If myStr = "" Then
'nothing checked
Else
myStr = Mid(myStr, Len(mySep) + 1)
End If

Worksheets("inv 1st page").Range("d42").Value = myStr
Unload UserForm1
Sheets("DATA SHEET").Select
Range("A111").Select

End Sub

Private Sub UserForm_Initialize()
Dim myRng As Range
With Worksheets("inputpage")
Set myRng = .Range("c1", .Cells(.Rows.Count, "c").End(xlUp))
End With

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption
.List = myRng.Value
End With
End Sub

================================================== ===

now i want to add this and get the message popup that say

unable to get the activate property of the range class
================================================== ===

Private Sub CommandButton1_Click()
Dim i As String
Dim Lrow As Variant
Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1,
0).Activate
If TextBox1.Value < "" Then
ActiveCell.Value = TextBox1.Value
End If
CommandButton1.Enabled = False

Hi

Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1, 0).Activate
If TextBox1.Value < "" Then ActiveCell.Value = Me.TextBox1.Value
Me.CommandButton1.Enabled = False
End Sub

or

Private Sub CommandButton1_Click()
Dim Lrow As String
Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1, 0).Address
If TextBox1.Value < "" Then Lrow.Value = Me.TextBox1.Value
Me.CommandButton1.Enabled = False
End Sub

Regards,

Per

GTVT06

unable to get the activate property of the range class

On Jan 12, 12:19*pm, pswanie
wrote:
still cant get it to work..

how and where in my code should i put it?

"Per Jessen" wrote:
On 12 Jan., 18:36, pswanie wrote:
im trying to

================================================== ===
what code do i add to a commandbutton2 for the next?

they enter name and lastname in the textbox. when hitting commandbutton2 i
need that data from textbox1 in the next open cell sheet1 column c down. and
then command button disable. refresh either userform1 or listbox1
================================================== ===

i got this code and it works
================================================== ===
Option Explicit
Private Sub CommandButton1_Click()
* * Dim myStr As String
* * Dim iCtr As Long
* * Dim mySep As String

* * mySep = ", "
* * myStr = ""
* * With Me.ListBox1
* * * * For iCtr = 0 To .ListCount - 1
* * * * * * If .Selected(iCtr) = True Then
* * * * * * * * myStr = myStr & ", " & .List(iCtr)
* * * * * * End If
* * * * Next iCtr
* * End With

* * If myStr = "" Then
* * * * 'nothing checked
* * Else
* * * * myStr = Mid(myStr, Len(mySep) + 1)
* * End If

* * Worksheets("inv 1st page").Range("d42").Value = myStr
* * Unload UserForm1
* * Sheets("DATA SHEET").Select
* * * * Range("A111").Select

End Sub

Private Sub UserForm_Initialize()
* * Dim myRng As Range
* * With Worksheets("inputpage")
* * * * Set myRng = .Range("c1", .Cells(.Rows.Count, "c").End(xlUp))
* * End With

* * With Me.ListBox1
* * * * .MultiSelect = fmMultiSelectMulti
* * * * .ListStyle = fmListStyleOption
* * * * .List = myRng.Value
* * End With
End Sub

*================================================= ====

* * * * * * *now i want to add this and get the message popup that say

* * * * * * *unable to get the activate property of the range class
================================================== ===

Private Sub CommandButton1_Click()
Dim i As String
Dim Lrow As Variant
* * Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1,
0).Activate
* * If TextBox1.Value < "" Then
* * ActiveCell.Value = TextBox1.Value
* * End If
* * CommandButton1.Enabled = False

Hi

Private Sub CommandButton1_Click()
* * Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1, 0).Activate
* * If TextBox1.Value < "" Then ActiveCell.Value = Me.TextBox1.Value
* * Me.CommandButton1.Enabled = False
End Sub

or

Private Sub CommandButton1_Click()
Dim Lrow As String
Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1, 0).Address
If TextBox1.Value < "" Then Lrow.Value = Me.TextBox1.Value
* * Me.CommandButton1.Enabled = False
End Sub

Regards,

Per- Hide quoted text -

- Show quoted text -

Put this in the code for Command Button 2

Private Sub CommandButton2_Click()
Dim Lrow As Variant
Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1,
0).Activate
If TextBox1.Value < "" Then
ActiveCell.Value = TextBox1.Value
End If
CommandButton2.Enabled = False
End Sub

pswanie

unable to get the activate property of the range class

jip i did.. but maybe im not putting it in the right place?

i got this code and it works
================================================== ===
Option Explicit
Private Sub CommandButton1_Click()
Dim myStr As String
Dim iCtr As Long
Dim mySep As String

mySep = ", "
myStr = ""
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) = True Then
myStr = myStr & ", " & .List(iCtr)
End If
Next iCtr
End With

If myStr = "" Then
'nothing checked
Else
myStr = Mid(myStr, Len(mySep) + 1)
End If

Worksheets("inv 1st page").Range("d42").Value = myStr
Unload UserForm1
Sheets("DATA SHEET").Select
Range("A111").Select

End Sub

Private Sub UserForm_Initialize()
Dim myRng As Range
With Worksheets("inputpage")
Set myRng = .Range("c1", .Cells(.Rows.Count, "c").End(xlUp))
End With

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption
.List = myRng.Value
End With
End Sub

================================================== ===

now i want to add this and get the message popup that say

unable to get the activate property of the range class
================================================== ===

Private Sub CommandButton1_Click()
Dim i As String
Dim Lrow As Variant
Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1,
0).Activate
If TextBox1.Value < "" Then
ActiveCell.Value = TextBox1.Value
End If
CommandButton1.Enabled = False

pswanie

unable to get the activate property of the range class

all my efforts come back to this line and get stuck on that

Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1, 0).Activate

"GTVT06" wrote:

On Jan 12, 12:19 pm, pswanie
wrote:
still cant get it to work..

how and where in my code should i put it?

"Per Jessen" wrote:
On 12 Jan., 18:36, pswanie wrote:
im trying to

================================================== ===
what code do i add to a commandbutton2 for the next?

they enter name and lastname in the textbox. when hitting commandbutton2 i
need that data from textbox1 in the next open cell sheet1 column c down. and
then command button disable. refresh either userform1 or listbox1
================================================== ===

i got this code and it works
================================================== ===
Option Explicit
Private Sub CommandButton1_Click()
Dim myStr As String
Dim iCtr As Long
Dim mySep As String

mySep = ", "
myStr = ""
With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) = True Then
myStr = myStr & ", " & .List(iCtr)
End If
Next iCtr
End With

If myStr = "" Then
'nothing checked
Else
myStr = Mid(myStr, Len(mySep) + 1)
End If

Worksheets("inv 1st page").Range("d42").Value = myStr
Unload UserForm1
Sheets("DATA SHEET").Select
Range("A111").Select

End Sub

Private Sub UserForm_Initialize()
Dim myRng As Range
With Worksheets("inputpage")
Set myRng = .Range("c1", .Cells(.Rows.Count, "c").End(xlUp))
End With

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption
.List = myRng.Value
End With
End Sub

================================================== ===

now i want to add this and get the message popup that say

unable to get the activate property of the range class
================================================== ===

Private Sub CommandButton1_Click()
Dim i As String
Dim Lrow As Variant
Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1,
0).Activate
If TextBox1.Value < "" Then
ActiveCell.Value = TextBox1.Value
End If
CommandButton1.Enabled = False

Hi

Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1, 0).Activate
If TextBox1.Value < "" Then ActiveCell.Value = Me.TextBox1.Value
Me.CommandButton1.Enabled = False
End Sub

or

Private Sub CommandButton1_Click()
Dim Lrow As String
Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1, 0).Address
If TextBox1.Value < "" Then Lrow.Value = Me.TextBox1.Value
Me.CommandButton1.Enabled = False
End Sub

Regards,

Per- Hide quoted text -

- Show quoted text -

Put this in the code for Command Button 2

Private Sub CommandButton2_Click()
Dim Lrow As Variant
Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1,
0).Activate
If TextBox1.Value < "" Then
ActiveCell.Value = TextBox1.Value
End If
CommandButton2.Enabled = False
End Sub

Per Jessen[_2_]

unable to get the activate property of the range class

On 12 Jan., 20:53, pswanie wrote:
all my efforts come back to this line and get stuck on that

Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1, 0).Activate

"GTVT06" wrote:
On Jan 12, 12:19 pm, pswanie
wrote:
still cant get it to work..

how and where in my code should i put it?

"Per Jessen" wrote:
On 12 Jan., 18:36, pswanie wrote:
im trying to

================================================== ===
what code do i add to a commandbutton2 for the next?

they enter name and lastname in the textbox. when hitting commandbutton2 i
need that data from textbox1 in the next open cell sheet1 column c down. and
then command button disable. refresh either userform1 or listbox1
================================================== ===

i got this code and it works
================================================== ===
Option Explicit
Private Sub CommandButton1_Click()
* * Dim myStr As String
* * Dim iCtr As Long
* * Dim mySep As String

* * mySep = ", "
* * myStr = ""
* * With Me.ListBox1
* * * * For iCtr = 0 To .ListCount - 1
* * * * * * If .Selected(iCtr) = True Then
* * * * * * * * myStr = myStr & ", " & .List(iCtr)
* * * * * * End If
* * * * Next iCtr
* * End With

* * If myStr = "" Then
* * * * 'nothing checked
* * Else
* * * * myStr = Mid(myStr, Len(mySep) + 1)
* * End If

* * Worksheets("inv 1st page").Range("d42").Value = myStr
* * Unload UserForm1
* * Sheets("DATA SHEET").Select
* * * * Range("A111").Select

End Sub

Private Sub UserForm_Initialize()
* * Dim myRng As Range
* * With Worksheets("inputpage")
* * * * Set myRng = .Range("c1", .Cells(.Rows.Count, "c").End(xlUp))
* * End With

* * With Me.ListBox1
* * * * .MultiSelect = fmMultiSelectMulti
* * * * .ListStyle = fmListStyleOption
* * * * .List = myRng.Value
* * End With
End Sub

*================================================= ====

* * * * * * *now i want to add this and get the message popup that say

* * * * * * *unable to get the activate property of the range class
================================================== ===

Private Sub CommandButton1_Click()
Dim i As String
Dim Lrow As Variant
* * Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1,
0).Activate
* * If TextBox1.Value < "" Then
* * ActiveCell.Value = TextBox1.Value
* * End If
* * CommandButton1.Enabled = False

Hi

Private Sub CommandButton1_Click()
* * Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1, 0).Activate
* * If TextBox1.Value < "" Then ActiveCell.Value = Me.TextBox1.Value
* * Me.CommandButton1.Enabled = False
End Sub

or

Private Sub CommandButton1_Click()
Dim Lrow As String
Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1, 0).Address
If TextBox1.Value < "" Then Lrow.Value = Me.TextBox1.Value
* * Me.CommandButton1.Enabled = False
End Sub

Regards,

Per- Hide quoted text -

- Show quoted text -

Put this in the code for Command Button 2

Private Sub CommandButton2_Click()
Dim Lrow As Variant
* * Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1,
0).Activate
* * If TextBox1.Value < "" Then
* * ActiveCell.Value = TextBox1.Value
* * End If
* * CommandButton2.Enabled = False
End Sub- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi

Put this in the Codesheet for the Userform

Private Sub CommandButton2_Click()
Dim Lrow As String
Lrow = Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1, 0).Address
If TextBox1.Value < "" Then Range(Lrow).Value = Me.TextBox1.Value
Me.CommandButton2.Enabled = False
End Sub

Regards,

Per

All times are GMT +1. The time now is 08:20 AM.