How to ask to select a cell in Excel and then read the position?

[Teeser]

Hi,

Here is the situation:

For use with VB6.0 and Excel2000 on a Win2K PC:

You have an Excel sheet with data.

With your VB application, you open the Excel workbook and you want to ask the user to select a cell in that sheet. And then you want to know the position of that cell, so you can read the values of the two cells at the right side of the selected cell.

The opening of the worksheet is no problem.

Thanks in advance...

 

[BrainB]

    Dim MyAddress As String
    Dim MyRow As Long
    Dim MyColumn As Integer
    '------------------------------
    MyAddress = ActiveCell.Address
    MyRow = ActiveCell.Row
    MyColumn = ActiveCell.Column

Regards
BrianB
Use CupOfCoffee to speed up all windows applications.
It is easy until you know how.
================================

 

[Molby]

As a note, you don't necessarily need to know where the active cell is to get the two values to the right. You can use

Activecell.Offset(0,1).Value
and
Activecell.Offset(0,2).Value

These will give the value of the two cells to the right

 

[Teeser]

Thanks already BrainB and Molby.
This already got me on the right track.

This works when the user first selects a cell and afterwords activates the determination of the position of the cell (by selecting a command button in the VB-app.).

But what would be needed if you want to work the other way around:

1. You first click a command button on your VB-application.
2. The Excel-file is opened.
3. You automatically ask the user to select a cell.
4. You automatically read the position of that cell without the user having to click an other command button in the VB-app.

Regards

 

[TonyJollans]

Hi Teeser,

To ask the user to select a cell, use ..

[blue]Set myCell = Application.InputBox("Please select a cell", Type:=8)[/blue]

myCell is then a Range so you can then use it as per previous posts instaed of activecell.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.