selecting cell using buttons

[rishilaughs]

Hi,

i am a newbie to macro and vb programming
i want to implement a small functionality of selecting a cell using buttons in a table
For E.g in a table like this

	Column Button A	Column Button B	Column Button C
Row Button A	13	14	15
Row Button B	16	17	18
Row Button C	19	20	21

<tbody>
</tbody>

i wish if i click row button b and then click column button c i want value 18 to be selected to be used for further calculation.

I am trying to use worksheet function row, column, match ,index and also using Worksheet_BeforeDoubleClick event but don't seem to be reaching to the desired output.

Any help would be highly appreciated

Thanking in anticipation
Rishi

 

[bertie]

Hi, and welcome to the forum.

This is a simple solution to give you an idea of how this could be done.

Declare two global variables:

Public rw As Long       'row number
Public cell As String    'column letter

For each button create a click event. For example, the "row" buttons will update the row variable:

Private Sub cmdRow1_Click()
   rw = 2
End Sub

The "column" buttons will assign the column letter, and add the row number.
Then it will call a test function to return the desired value:

Private Sub cmdColA_Click()
   cell = "A" & rw
   
   'test
   MsgBox GetCellValue(cell)
End Sub

A simple function to get the value could be:

Function GetCellValue(ByVal CellAddress As String) As Variant
   GetCellValue = Sheets("Sheet1").Range(CellAddress).Value
End Function

Hope this helps get you started.
Bertie

 

[p45cal]

Using activex buttons on the sheet, in the sheet's code module:

Dim Colm As Long, rw As Long
Private Sub CommandButton1_Click() 'Column B button
Colm = 2
selectme
End Sub

Private Sub CommandButton2_Click() 'Column C button
Colm = 3
selectme
End Sub

Private Sub CommandButton3_Click() 'Column D button
Colm = 4
selectme
End Sub

Private Sub CommandButton4_Click() 'row 2 button
rw = 2
selectme
End Sub

Private Sub CommandButton5_Click() 'row 3 button
rw = 3
selectme
End Sub

Private Sub CommandButton6_Click() 'row 4 button
rw = 4
selectme
End Sub
Sub selectme()
If rw > 0 And Colm > 0 Then Cells(rw, Colm).Select
End Sub