named range problem
 

I am having a unique problem with this code.PLease help.

Private Sub CommandButton1_Click()
Dim rg1 As Range
Dim rg2 As Range
Dim rg3 As Range
Dim rg As Range
Dim lstrow As Range
Dim lstclm As Range


On Error Resume Next
Set rg1 = Range(TextBox1.Text)
If Err.Number < 0 Then
On Error GoTo 0
Set lstrow = Range("a1500").End(xlUp)
lstrow.Offset(1, 0).Value = TextBox1.Text
lstrow.Offset(1, 0).EntireRow.Select
Selection.Name = TextBox1.Text
End If
On Error Resume Next
Set rg2 = Range("A" & TextBox2.Text)
If Err.Number < 0 Then
On Error GoTo 0
Set lstclm = Range("iv1").End(xlToLeft)
lstclm.Offset(0, 1).Value = TextBox2.Text
lstclm.Offset(0, 1).EntireColumn.Select
Selection.Name = "A" & TextBox2.Text
lstclm.Offset(0, 2).Value = TextBox2.Text & "OT"
lstclm.Offset(0, 2).EntireColumn.Select
Selection.Name = "A" & TextBox2.Text & "OT"
End If

On Error GoTo 0
Set rg1 = Range(TextBox1.Text)
Set rg2 = Range("A" & TextBox2.Text)
Set rg3 = Range("A" & TextBox2.Text & "OT")
Set rg = Intersect(rg1, rg2)
rg.Select
If TextBox3.Text < "" Then
If Selection.Value < "" Then
MsgBox ("Duplicate")
Exit Sub
End If
Selection.Value = TextBox3.Text
End If
Set rg = Intersect(rg1, rg3)
rg.Select
If TextBox4.Text < "" Then
If Selection.Value < "" Then
MsgBox ("Duplicate")
Exit Sub
End If
Selection.Value = TextBox4.Text
End If
TextBox1.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
End Sub

when the value of textbox2 is 052805 then the code fails when rg3 is set. It
does not with other values.

named range problem
 

R,

Have you set the TakeFocusOnClick property of the commandbutton to False?

HTH,
Bernie
MS Excel MVP


"Rbp9ad" wrote in message
...
I am having a unique problem with this code.PLease help.

Private Sub CommandButton1_Click()
Dim rg1 As Range
Dim rg2 As Range
Dim rg3 As Range
Dim rg As Range
Dim lstrow As Range
Dim lstclm As Range


On Error Resume Next
Set rg1 = Range(TextBox1.Text)
If Err.Number < 0 Then
On Error GoTo 0
Set lstrow = Range("a1500").End(xlUp)
lstrow.Offset(1, 0).Value = TextBox1.Text
lstrow.Offset(1, 0).EntireRow.Select
Selection.Name = TextBox1.Text
End If
On Error Resume Next
Set rg2 = Range("A" & TextBox2.Text)
If Err.Number < 0 Then
On Error GoTo 0
Set lstclm = Range("iv1").End(xlToLeft)
lstclm.Offset(0, 1).Value = TextBox2.Text
lstclm.Offset(0, 1).EntireColumn.Select
Selection.Name = "A" & TextBox2.Text
lstclm.Offset(0, 2).Value = TextBox2.Text & "OT"
lstclm.Offset(0, 2).EntireColumn.Select
Selection.Name = "A" & TextBox2.Text & "OT"
End If

On Error GoTo 0
Set rg1 = Range(TextBox1.Text)
Set rg2 = Range("A" & TextBox2.Text)
Set rg3 = Range("A" & TextBox2.Text & "OT")
Set rg = Intersect(rg1, rg2)
rg.Select
If TextBox3.Text < "" Then
If Selection.Value < "" Then
MsgBox ("Duplicate")
Exit Sub
End If
Selection.Value = TextBox3.Text
End If
Set rg = Intersect(rg1, rg3)
rg.Select
If TextBox4.Text < "" Then
If Selection.Value < "" Then
MsgBox ("Duplicate")
Exit Sub
End If
Selection.Value = TextBox4.Text
End If
TextBox1.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
End Sub

when the value of textbox2 is 052805 then the code fails when rg3 is set. It does not with other
values.

named range problem
 

I did this and it did not work. Also another value gives me the same problem
062505. I am confused on this one.
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
R,

Have you set the TakeFocusOnClick property of the commandbutton to False?

HTH,
Bernie
MS Excel MVP


"Rbp9ad" wrote in message
...
I am having a unique problem with this code.PLease help.

Private Sub CommandButton1_Click()
Dim rg1 As Range
Dim rg2 As Range
Dim rg3 As Range
Dim rg As Range
Dim lstrow As Range
Dim lstclm As Range


On Error Resume Next
Set rg1 = Range(TextBox1.Text)
If Err.Number < 0 Then
On Error GoTo 0
Set lstrow = Range("a1500").End(xlUp)
lstrow.Offset(1, 0).Value = TextBox1.Text
lstrow.Offset(1, 0).EntireRow.Select
Selection.Name = TextBox1.Text
End If
On Error Resume Next
Set rg2 = Range("A" & TextBox2.Text)
If Err.Number < 0 Then
On Error GoTo 0
Set lstclm = Range("iv1").End(xlToLeft)
lstclm.Offset(0, 1).Value = TextBox2.Text
lstclm.Offset(0, 1).EntireColumn.Select
Selection.Name = "A" & TextBox2.Text
lstclm.Offset(0, 2).Value = TextBox2.Text & "OT"
lstclm.Offset(0, 2).EntireColumn.Select
Selection.Name = "A" & TextBox2.Text & "OT"
End If

On Error GoTo 0
Set rg1 = Range(TextBox1.Text)
Set rg2 = Range("A" & TextBox2.Text)
Set rg3 = Range("A" & TextBox2.Text & "OT")
Set rg = Intersect(rg1, rg2)
rg.Select
If TextBox3.Text < "" Then
If Selection.Value < "" Then
MsgBox ("Duplicate")
Exit Sub
End If
Selection.Value = TextBox3.Text
End If
Set rg = Intersect(rg1, rg3)
rg.Select
If TextBox4.Text < "" Then
If Selection.Value < "" Then
MsgBox ("Duplicate")
Exit Sub
End If
Selection.Value = TextBox4.Text
End If
TextBox1.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
End Sub

when the value of textbox2 is 052805 then the code fails when rg3 is set.
It does not with other values.

named range problem
 

Try this shortened version:

Private Sub CommandButton1_Click()
Dim rg3 As Range
Dim Textbox2 As String
Textbox2 = "052805"
Selection.Name = "A" & Textbox2 & "OT"
Set rg3 = Range("A" & Textbox2 & "OT")
MsgBox "The name " & rg3.Name & " is assigned to " & rg3.Address
End Sub

HTH,
Bernie
MS Excel MVP


"Rbp9ad" wrote in message
...
I did this and it did not work. Also another value gives me the same problem 062505. I am confused
on this one.
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
R,

Have you set the TakeFocusOnClick property of the commandbutton to False?

HTH,
Bernie
MS Excel MVP


"Rbp9ad" wrote in message
...
I am having a unique problem with this code.PLease help.

Private Sub CommandButton1_Click()
Dim rg1 As Range
Dim rg2 As Range
Dim rg3 As Range
Dim rg As Range
Dim lstrow As Range
Dim lstclm As Range


On Error Resume Next
Set rg1 = Range(TextBox1.Text)
If Err.Number < 0 Then
On Error GoTo 0
Set lstrow = Range("a1500").End(xlUp)
lstrow.Offset(1, 0).Value = TextBox1.Text
lstrow.Offset(1, 0).EntireRow.Select
Selection.Name = TextBox1.Text
End If
On Error Resume Next
Set rg2 = Range("A" & TextBox2.Text)
If Err.Number < 0 Then
On Error GoTo 0
Set lstclm = Range("iv1").End(xlToLeft)
lstclm.Offset(0, 1).Value = TextBox2.Text
lstclm.Offset(0, 1).EntireColumn.Select
Selection.Name = "A" & TextBox2.Text
lstclm.Offset(0, 2).Value = TextBox2.Text & "OT"
lstclm.Offset(0, 2).EntireColumn.Select
Selection.Name = "A" & TextBox2.Text & "OT"
End If

On Error GoTo 0
Set rg1 = Range(TextBox1.Text)
Set rg2 = Range("A" & TextBox2.Text)
Set rg3 = Range("A" & TextBox2.Text & "OT")
Set rg = Intersect(rg1, rg2)
rg.Select
If TextBox3.Text < "" Then
If Selection.Value < "" Then
MsgBox ("Duplicate")
Exit Sub
End If
Selection.Value = TextBox3.Text
End If
Set rg = Intersect(rg1, rg3)
rg.Select
If TextBox4.Text < "" Then
If Selection.Value < "" Then
MsgBox ("Duplicate")
Exit Sub
End If
Selection.Value = TextBox4.Text
End If
TextBox1.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
End Sub

when the value of textbox2 is 052805 then the code fails when rg3 is set. It does not with other
values.

named range problem
 

I do not need to know this, the project I was using it for is completed. The
named range was to be made up off the the date in ddmmyy no dashes or
slashes with an A in front and OT in the back. It would work fine for dates
between july and december. Any dates from the first half of the year did not
work. I made a workaround by just entering dates before july with the first
two letters of the month. I am just trying to understand why this happened?
Why would only half the values work? In excel I can apply these names but
not from the code.

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Try this shortened version:

Private Sub CommandButton1_Click()
Dim rg3 As Range
Dim Textbox2 As String
Textbox2 = "052805"
Selection.Name = "A" & Textbox2 & "OT"
Set rg3 = Range("A" & Textbox2 & "OT")
MsgBox "The name " & rg3.Name & " is assigned to " & rg3.Address
End Sub

HTH,
Bernie
MS Excel MVP


"Rbp9ad" wrote in message
...
I did this and it did not work. Also another value gives me the same
problem 062505. I am confused on this one.
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
R,

Have you set the TakeFocusOnClick property of the commandbutton to
False?

HTH,
Bernie
MS Excel MVP


"Rbp9ad" wrote in message
...
I am having a unique problem with this code.PLease help.

Private Sub CommandButton1_Click()
Dim rg1 As Range
Dim rg2 As Range
Dim rg3 As Range
Dim rg As Range
Dim lstrow As Range
Dim lstclm As Range


On Error Resume Next
Set rg1 = Range(TextBox1.Text)
If Err.Number < 0 Then
On Error GoTo 0
Set lstrow = Range("a1500").End(xlUp)
lstrow.Offset(1, 0).Value = TextBox1.Text
lstrow.Offset(1, 0).EntireRow.Select
Selection.Name = TextBox1.Text
End If
On Error Resume Next
Set rg2 = Range("A" & TextBox2.Text)
If Err.Number < 0 Then
On Error GoTo 0
Set lstclm = Range("iv1").End(xlToLeft)
lstclm.Offset(0, 1).Value = TextBox2.Text
lstclm.Offset(0, 1).EntireColumn.Select
Selection.Name = "A" & TextBox2.Text
lstclm.Offset(0, 2).Value = TextBox2.Text & "OT"
lstclm.Offset(0, 2).EntireColumn.Select
Selection.Name = "A" & TextBox2.Text & "OT"
End If

On Error GoTo 0
Set rg1 = Range(TextBox1.Text)
Set rg2 = Range("A" & TextBox2.Text)
Set rg3 = Range("A" & TextBox2.Text & "OT")
Set rg = Intersect(rg1, rg2)
rg.Select
If TextBox3.Text < "" Then
If Selection.Value < "" Then
MsgBox ("Duplicate")
Exit Sub
End If
Selection.Value = TextBox3.Text
End If
Set rg = Intersect(rg1, rg3)
rg.Select
If TextBox4.Text < "" Then
If Selection.Value < "" Then
MsgBox ("Duplicate")
Exit Sub
End If
Selection.Value = TextBox4.Text
End If
TextBox1.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
End Sub

when the value of textbox2 is 052805 then the code fails when rg3 is
set. It does not with other values.