unable to get the activate property of the range class

P

pswanie

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
 
P

Per Jessen

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
Click to expand...

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
 
G

GTVT06

still cant get it to work..

how and where in my code should i put it?





- Show quoted text -
Click to expand...

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
 
P

pswanie

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

i got this code and it works
 
P

pswanie

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

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

Per Jessen

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

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







- Vis tekst i anførselstegn -
Click to expand...

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
 

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

Similar Threads

MsgBox. problem to continue the macro 6
multiple cells text to text in one cell 3
Data Val MultiSelect on Userform? 2
How to obtain the row count of a QueryTable from a Web query 4
copy selected cells to new sheet. 12
Editing text copied into a cell by a UserForm 2
Run-time error '9': Subscript out of range 6
Array in VBA 2

Top