named range problem

[Rbp9ad]

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.

 

[Bernie Deitrick]

R,

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

HTH,
Bernie
MS Excel MVP

 

[Rbp9ad]

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]

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]

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.