Can we know which button was pressed on the excel sheet when the buttons are created during run time???

[nitind]

I have created a button name "Add Power Source" on excel sheet that adds a table and a button(named "Add Row") to the sheet.Whenever the user clicks the "add power source" button a table and a "Add new row " button is created .This "Add new row" button is used to add rows to the table.
Now suppose the user has pressed the "add power source" button 5 times,so that there will be five table on sheet and each having a "add new row " button associated with it.When the user wants to add a new row to the table 2 say .How will I come to know in which table the rows are to be added using VBA.

 

[RoryA]

If you are using buttons from the Forms toolbar, then your macro can use:

activesheet.shapes(application.caller)

to refer to the button being pressed.

 

[nitind]

will it tell me the name of the button ???

 

[RoryA]

Application.Caller will return the name of the button. Using that within the Shapes collection will give you a reference to the actual button. You can then use properties like:

activesheet.shapes(application.caller).TopLeftCell

to work out where on the sheet the button is.

 

[nitind]

Thanks a lot! It works.
Can I change the name(not caption) of the button also??
Can I get the cell location from the pixel location the button??

 

[RoryA]

Yes - just select the button and name it in the name box on the left of the formula bar.
The TopLeftCell property I mentioned above gives you the cell underneath the top left corner of the button.

 

[nitind]

but i have to do it using vba.Can you suggest a solution to complete problem I ll send u what i ve coded after sometime

 

[nitind]

How can we see the property window of a button on the excel sheet??

 

[RoryA]

The answer to both those questions depend on the code you have now!

 

[nitind]

Dim columnIndex As String, rowIndex As Integer 'for holding the cell from where paste operation should start
Dim current_no_of_power_sources As Integer 'to count total no
'of power sources

Dim rngFirstCellTable As Range ' variable to hold the first cell
'of first row of the table
'-----------------------------------------------------------------
'Purpose : To add a new row to the component table of power source
'macro for adding new row to table2
Sub cmdAddNewRow_Click()

Dim firstColumn As String
firstColumn = "C"
Dim Count As Integer
Dim no_of_rows As String
Dim current_no_of_rows As Integer 'to hold the count of rows
'currently present in the table2

Dim cell_name As String 'to hold a cell name
Dim temp As Integer 'to hold the row no of an empty row
Dim VoMinCell 'to get the address of column min
'output voltage VominCell of table1

Dim VoMaxCell 'to get the address of column max
'output voltage VoMaxCell of table1
current_no_of_rows = 0 'to count the no of rows currently
'present before any empty row in table2


Set rngFirstCellTable = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Offset(4, 0)
Set VoMinCell = Worksheets("Sheet1").Range(rngFirstCellTable.Offset(-8, 2).Address())

Set VoMaxCell = Worksheets("Sheet1").Range(rngFirstCellTable.Offset(-8, 3).Address())

rngFirstCellTable.Select 'to select first cell (next row
'to header)of table 2



'loop to select last row in the table2

Do Until Selection.Value = "" And Selection.Offset(0, 1).Value = "" And _
Selection.Offset(0, 2).Value = "" And Selection.Offset(0, 3).Value = ""
Selection.Offset(1, 0).Select
current_no_of_rows = current_no_of_rows + 1
Loop



no_of_rows = InputBox("How many rows")
If no_of_rows = "" Then Exit Sub


'loop that will add rows to the table starting from the last entry of table

For Count = 0 To no_of_rows - 1
Selection.EntireRow.Insert
current_no_of_rows = current_no_of_rows + 1

Selection.EntireRow.FillDown
If current_no_of_rows = 2 Then
Selection.Offset(0, 5).Value = ""
End If
'apply formula to the second row
If current_no_of_rows = 2 Then
rngFirstCellTable.Offset(1, 5).Formula = _
"=IF(and(VoMinCell >rngFirstCellTable.Offset(0, 3) , VoMaxCell<rngFirstCellTable.Offset(0, 4) ),""OK"", ""NOK"" )"
End If

Selection.Value = Selection.Offset(-1, 0) + 1
Selection.Offset(0, 1).Value = ""
Selection.Offset(0, 2).Value = ""
Selection.Offset(0, 3).Value = ""
Selection.Offset(0, 4).Value = ""
Selection.Value = ""


temp = rngFirstCellTable.Row + (current_no_of_rows - 1)

cell_name = firstColumn & temp
Range(cell_name).Select
Selection.Offset(0, 1).Value = Selection.Offset(-1, 1) + 1


Selection.Value = Selection.Offset(-1, 0).Value + 1 ' to set serial no of
'row in table 2
Selection.Offset(1, 0).Select
'Selection.AllowEdit = False
Next Count
End Sub

'Purpose: To add a power source to the sheet
'Macro for Power Source BUtton

Sub AddPowerSource()
Dim i As Integer, no_of_power_sources As String
Dim cellName As String 'from where will the paste
operation starts

If current_no_of_power_sources = 0 Then
columnIndex = "C" ' first power source location top-left
rowIndex = 18
End If
no_of_power_sources = InputBox("How many power sources do you want")
If no_of_power_sources = "" Then Exit Sub
current_no_of_power_sources = current_no_of_power_sources + no_of_power_sources
For i = 1 To no_of_power_sources
cellName = columnIndex & rowIndex
Sheets("Table_container").Select
Range("C6:M21").Select
Selection.Copy
Sheets("Sheet1").Select
Range(cellName).Select

'ActiveSheet.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:= _
'False, Transpose:=False
' ActiveSheet.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, SkipBlanks:= _
'False, Transpose:=False
'ActiveSheet.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, SkipBlanks:= _
'False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
rowIndex = rowIndex + 24
Next i
End Sub