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

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?

"art" wrote:

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

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

On 2 Nov., 17:48, art wrote:
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

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.


art wrote:

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

--

Dave Peterson

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

--


Gary

"art" wrote in message
...
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

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?

"JLGWhiz" wrote:

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?

"art" wrote:

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

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



"art" wrote:

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

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?



"JLGWhiz" wrote:

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



"art" wrote:

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

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

--
Rick (MVP - Excel)


"art" wrote in message
...
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?

"JLGWhiz" wrote:

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?

"art" wrote:

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

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

"Dave Peterson" wrote:

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.


art wrote:

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

--

Dave Peterson

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



"art" wrote:

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?



"JLGWhiz" wrote:

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



"art" wrote:

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

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)

"art" wrote:

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

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

"JLGWhiz" wrote:

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



"art" wrote:

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?



"JLGWhiz" wrote:

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



"art" wrote:

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

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

--
Rick (MVP - Excel)


"art" wrote in message
...
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)

"art" wrote:

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

Have you tried any of the solutions suggested ?

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

--
Per

On 2 Nov., 19:39, art wrote:
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)



"art" wrote:
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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

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.

"JLGWhiz" wrote:

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



"art" wrote:

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?



"JLGWhiz" wrote:

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



"art" wrote:

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

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

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

--
Per

On 2 Nov., 20:11, art wrote:
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.



"JLGWhiz" wrote:
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

"art" wrote:

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?

"JLGWhiz" wrote:

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

"art" wrote:

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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Look for .cells(1) in the previous post.

art wrote:

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

"Dave Peterson" wrote:

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.


art wrote:

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

--

Dave Peterson


--

Dave Peterson

no, this line is the error, myCell = Range(rngAddr).Range("A1").Address

"Per Jessen" wrote:

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

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

--
Per

On 2 Nov., 20:11, art wrote:
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.



"JLGWhiz" wrote:
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

"art" wrote:

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?

"JLGWhiz" wrote:

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

"art" wrote:

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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

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.

"art" wrote:

no, this line is the error, myCell = Range(rngAddr).Range("A1").Address

"Per Jessen" wrote:

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

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

--
Per

On 2 Nov., 20:11, art wrote:
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.



"JLGWhiz" wrote:
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

"art" wrote:

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?

"JLGWhiz" wrote:

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

"art" wrote:

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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi,

I copied the code into a userform and it worked fine.

While in Debug mode put your cursor over rngAddr and see what Excel is
storing in the variable. If you haven't selected a cell in the RefEdit box
this will cause an error.

--
Thanks,
Shane Devenshire


"art" wrote:

no, this line is the error, myCell = Range(rngAddr).Range("A1").Address

"Per Jessen" wrote:

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

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

--
Per

On 2 Nov., 20:11, art wrote:
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.



"JLGWhiz" wrote:
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

"art" wrote:

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?

"JLGWhiz" wrote:

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

"art" wrote:

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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -