Excel VBA - Selecting a Row 2

[deesheeha]

Hi,

Im pretty new to Excel VBA but have a good knowledge of Access VBA. My problem is the following:

On every second row i have added a button in the first cell of the row. When i click on it, i want it to equal the row that the button is on. How do i capture not the row of what cell is selected but capture the row where the button is placed.

Any help is great!

Dee

 

[SkipVought]

Dee,

msgbox ButtonObject.topleftcell.row & " is the row"

Skip,

_[red][/red]

 

[deesheeha]

Im so confused. I dont know what im doing to be honest. I thought because I could code Access VBA I'd be ok for Excel (obviously not )

Thanks for the reply skip. Where do I put that line of code... is it in the macro for that button click?? is there any other code needed??

 

[SkipVought]

Yea, in the button_click event.

So how many COLUMNS in the ROW will you be referencing?

What do you intend to do with the referenced range?

Skip,

_[red][/red]

 

[deesheeha]

hmmmm, basically ive put a button over the cell but as i feel there is nothing to really connect it to the cell its over.

all i have done is a button over cell A1, A3, A5...

All i want to do is when the button is clicked, a variable becomes equal to the row that its on

Thanks again Skip

Dee

 

[SkipVought]

Equal to WHAT? What's the variable and what are the COLUMNS that you want, what, summed?

Skip,

_[red][/red]

 

[deesheeha]

Sorry Skip!!

Im not being clear. I want it to be equal to the row number. so if its row 16 then the variable becomes equal to 16

 

[SkipVought]

OK, That's waht was in my code then.

Is that it?

Skip,

_[red][/red]

 

[deesheeha]

It seems to be right. It spits an error but i can figure it out. Thanks for your help skip

 

[DaleWatson123321]

Dee,

I occasionally get an opportunity to help Skip.

Where Skip refers to "ButtonObject", he means for you to replace "ButtonObject" with the actual name of the button.

You can identify the name of the button by right-clicking on the button, and looking in the "Name Box" (top-left-corner - above the "A" identifying the first column).

The following are examples you can use for the three buttons...

Sub Button_1()
    MsgBox [Button 1].TopLeftCell.Row & " is the row"
End Sub

Sub Button_2()
    MsgBox [Button 2].TopLeftCell.Row & " is the row"
End Sub

Sub Button_3()
    MsgBox [Button 3].TopLeftCell.Row & " is the row"
End Sub

Hope this helps get you started. I'll let Skip carry on from here.

Regards, Dale Watson

 

[deesheeha]

hahah, thats perfect!! thanks Dale!! And thanks again Skip! I hate being a newbie at a language i already know!!

 

[Gavona]

hahah, thats perfect!! thanks Dale!! And thanks again Skip!

Why not thank in the proper way then? click on:
"Thank deesheeha
for this valuable post!"



Gavin

 

[combo]

Taking the opportunity you use old style 'Forms' buttons, you can use single macro for all buttons:

Sub ButtonClick()
MsgBox ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row & " is the row"
End Sub

combo

 

[DaleWatson123321]

combo,

Nice addition ===> STAR

Regards, Dale Watson

 

[SkipVought]

I like it, too! ==> *

Skip,

_[red][/red]

 

[combo]

Thanks all