Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > unable to get the activate property of the range class
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 247
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 703
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 247
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 247
Default 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 247
Default 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

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 703
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Unable to set NumberFormat Property of Range class Ade Excel Programming 0 March 28th 07 03:07 AM
Unable to set NumberFormat Property of Range class Gary''s Student Excel Programming 0 March 28th 07 02:43 AM
Unable to set FormulaArray property of the Range class [email protected] Excel Programming 2 October 17th 05 08:27 PM
Unable to set the Locked property of the range class Stuart[_5_] Excel Programming 5 June 25th 04 03:32 PM
Unable to set the Locked Property of the Range Class Stuart[_5_] Excel Programming 0 July 15th 03 06:59 PM


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

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement