Cell index?

[Gus Chuch]

When you open an excel document which properties do you set so the form will
open also ( I’ve got an form with some textboxes and command button’s on it).
And how do you setfocus back on the excel worksheet (sheet1). I need to know
the index of the cell that is clicked on.
I’m working on a word game for my kids and I got the main part of the code
done, just need to do some fine-tuning

 

[sebastienm]

Hi,

To start your code when opening a xl file, put your code (eg:
Userform1.Show) in the ThisWorkbook code module, in the event handler:
Private Sub Workbook_Open()
Userform1.show
End Sub

For your 2nd question, do you mean you want the user to be able to click the
active sheet while the form is displayed?
If so, 3 ways:

1- make the form modeless
UserForm1.Show vbModeless

2- use a RefEdit box control. It is the same contro as when you go in menu
Insert > Name > Define, box 'Refers To'. It has a red square on the right
which lets you hide the form and select a range.
To be able to use this control, right-click on the Toolbox toolbar >
Additional Control, select the RefEdt.Ctl control.

3- if possible, replace the form by a (floating) toolbar.

I hope this helps,

 

[Gus Chuch]

Ok that all worked out great, but how do I read the current cells position.
It would be the highlighted cell on the worksheet. I need this position for
the stating point in an for-next loop.
‘my test event to read the current position
Private Sub cmdShowPosition_Click()
txtPosition.Value = Sheet1. ActiveCell.LocationInTable
End Sub

 

[sebastienm]

Try:

Dim rg as range

''' return the selected cell(s) - may be multiple cells
Set rg= Selection

''' within these selected cells, one is highlighted in white.
''' It is the active cell (single cell)
Set rg=ActiveCell

''' to get the row or column numbers:
Debug.print rg.row & " - " & rg.column

 

[Gus Chuch]

Seem's to work great.
So range must be an object and we are creating and instance of that object
with
Dim rg As Range
I didn’t know excel had VB capabilities like this, any suggested reading or
text books that you recommend

 

[sebastienm]

Exactly.

A few books:
- "Excel 200x Power Programming with VBA"
- "Professional Excel Development"
- "Excel 200x VBA Programmer's reference"
More on amazon.com, search for: VBA Excel

Other learning methods:
- this newsgroup
- help system: search for 'Excel Object Model' - good starting point to see
how xl objects (more than 120 of them) relate to each other (also click to
drill-down).
- object brower (key F2): info about an object , sub, function... (from
there press F1 for details)
- many websites like http://www.contextures.com/tiptech.html (from there,
in the E section, you'll find links to other sites