Refer to first cell in range

A

art

Hello:

I have a userform with a refedit. I need to refer to the first cell (top) in
the rnage how can I refer to it?

Also How can I retrict the user to select only 1 column at a time, meaning
he can select a range from column C from row 1 to row 10000... but he cannot
also select C1 to D5?

Thanks
 
J

JLGWhiz

This will take care of the multicolumn selection. Put it
in the worksheet code module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Columns.Count > 1 Then
Selection.Columns(1).Select
End If
End Sub

I don't understand your first question. You need to rephrase it to
specify what the image is, Sheet, Shape, Picture, Selected Range?
 
P

Per Jessen

Maybe this will help you:

fCell = Selection.Cells(1, 1).Address
If Selection.Columns.Count > 1 Then
' more than one column was selected
msg = MsgBox("You can only select one column", vbInformation,
"Invalid selection")
Exit Sub
End If

Regards,
Per
 
D

Dave Peterson

Maybe you can let them select whatever they want and then just limit the
selection to a single cell or single column.

Option Explicit
Private Sub CommandButton1_Click()
Dim TestRng As Range
Dim SingleCell As Range
Dim SingleColumn As Range
Set TestRng = Nothing
On Error Resume Next
Set TestRng = Application.Range(Me.RefEdit1.Value)
On Error GoTo 0

If TestRng Is Nothing Then
'no range selected, what should happen
MsgBox "Not a range!"
Else
Set SingleCell = TestRng.Cells(1)
Set SingleColumn = TestRng.Columns(1)
MsgBox SingleCell.Address & vbLf & SingleColumn.Address
End If

Unload Me '???
End Sub

But you could do some validation...

if testrng.areas.count > 1 _
or testrng.columns.count > 1 _
or testrng.cells.count > 1 _
or testrng.row < 12 _
or testrng.column < 17 then


You can mix or match what you want to check.
 
G

Gary Keramidas

not sure what you mean in your first question, but if you have a range named
"test", this will select the first cell in the range:

range("test").Range("A1").Select
 
A

art

The refedit is where the user enter a range. I need to refer to the first
cell in the range. How can I refer to that?
 
J

JLGWhiz

Sorry, my eyes are going bad. You did specify range. I still don't
understand the question.
 
A

art

I'll explain. The user selects a range say from A1:A25 thru the refedit. Then
I do something with the range. But then I need to do something with the first
cell in the range (A1) so how can refer to thta? Am I clear enough?
 
R

Rick Rothstein

In general, you can use the Cells method of the range to get to any cell
within the range. In particular, the first cell in the range can be referred
this way...

Debug.Print Range(RefEdit).Cells(1, 1).Value

As an aside, this can be shortened this way if desired...

Debug.Print Range(RefEdit)(1, 1).Value

which, because you are interested in only the first column, can be shortened
even further like this...

Debug.Print Range(RefEdit)(1).Value
 
A

art

What about refering to the first cell in the range? How can I refer to that?
Please help.
 
J

JLGWhiz

OK, I got it now. The only thing is that after a user chooses a range
through the RefEdit and then closes the form, the value of the RefEdit is
also gone. You would have to capture the RefEdit value in a variable while
the form is open and pass that variable to your other code to be able to work
with it later. I think Rick gave you some code that would work while the
form is still open. Here is some more that will work while the form is open.
You can put it in a click event somewhere on the form, button or any control
including the form itself.

Dim rngAddr As String, myCell As String
rngAddr = Me.RefEdit1.Value
myCell = Range(rngAddr).Range("A1").Address
Range(myCell).Select
 
A

art

Why does nobody reply? Doe snobody under stand my question? Please answer, I
need it urgent. HOw can I refer to the first cell in a range. I don't know
what the range will be so I cannot say Range(A1)
 
A

art

Ok. Thanks. Now how do I enter the value from the textbox in the same
userfrom to that cell (first from Range).
 
R

Rick Rothstein

I'm not sure why you (apparently) cannot see my first answer, so I will try
again. Here is what I posted back to you about an hour ago...

In general, you can use the Cells method of the range to get to any cell
within the range. In particular, the first cell in the range can be referred
this way...

Debug.Print Range(RefEdit).Cells(1, 1).Value

As an aside, this can be shortened this way if desired...

Debug.Print Range(RefEdit)(1, 1).Value

which, because you are interested in only the first column, can be shortened
even further like this...

Debug.Print Range(RefEdit)(1).Value
 
P

Per Jessen

Have you tried any of the solutions suggested ?

I'm sure you have got the desired solution in previous posts
 
A

art

I get this error for this code:

If Categories.CheckBox1 = True Then
Dim rngAddr As String, myCell As String
rngAddr = Me.RefEdit1.Value
myCell = Range(rngAddr).Range("A1").Address
Range(myCell) = TextBox1.Text
End If

Run time error 1004 method Range of object global failed. Why is it not
good? Please help.
 
P

Per Jessen

As you don't tell which line throws the error, I think this shold do
it:

Range(myCell).value = me.Textbox1.Value
 
J

JLGWhiz

The only reason that I can think of is that the ActiveSheet when you click
the CheckBox is not the same sheet that he RefEdit range was on. The code
worked for me using the UserForm_Click and CommandButton_Click events with
the code in the UserForm code moduile. It has to run while the form is open.
 
Top