Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to set NumberFormat Property of Range class | Excel Programming | |||
Unable to set NumberFormat Property of Range class | Excel Programming | |||
Unable to set FormulaArray property of the Range class | Excel Programming | |||
Unable to set the Locked property of the range class | Excel Programming | |||
Unable to set the Locked Property of the Range Class | Excel Programming |