Rename control button name (name property) in excel vba
I used vba code to add new row using control button on column A.
the code works fine but has a small problem.
I'm trying to change the name property of a button while copying, but I don't know how?
For example, I am copying a button named "validate" when it is copied to the next line. I want to change the Name property (not the button text) of the "validate1" button.
can you tell me how to do this?
Dim Lr As Integer
Dim newLr As Integer
Dim lim, rng, sht, btn As String
Lr = Range("B" & Rows.Count).End(xlUp).Row 'Searching last row in column A
newLr = Lr + 1
lim = "B" & newLr & ":" + "D" & newLr
rng = "A" & newLr
Rows(Lr).Copy
Rows(newLr).Insert
'Range(lim).ClearContents
sht = ActiveSheet.Name
btn = "validate" & newLr
Application.ScreenUpdating = False
Sheets(sht).Shapes("validate").Copy
Sheets(sht).Activate
Sheets(sht).Range(rng).Select
Sheets(sht).Paste
Sheets(sht).Shapes("validate").Select
Selection.Characters.Text = btn
Application.ScreenUpdating = True
Image link: https://ibb.co/c0rFfv
source to share
After inserting the form, just write the code below.
Sheets(sht).Shapes(.Shapes.Count).Name = btn
Because when you add a new form, it has the highest index. So if you find the highest indexed form at .Shapes.Count
, then you can easily rename it.
And some other guidelines based on the best VBA guidelines:
1 - Always use Option Explicit
Because if you used it, you will see that your variables "lim,rng,sht"
were not defined. Commas = ",'
not enough to define all of your variables. You must declare them separately one at a time. So instead Dim lim, rng, sht, btn As String
useDim lim as String, rng as String, sht as String, btn As String
2 - Working with Long instead of Integer
As Excel may require Integer to continue working with code in newer versions of Excel. You can simply avoid this at first by specifying your variables as Long and not Integer.
3 - Never guess a worksheet
Do not rely on ActiveWorkbook or ActiveSheet as they can be modified by the user.
The best practice is to always determine which sheet your code is referring to:
So in your example:
Dim wb as Workbook, ws as Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheet("Sheet1")
Lr = ws.Range("B" & Rows.Count).End(xlUp).Row
some approach never misleads you.
4 - Avoid using Select or Activate
... Selection () slow
... Pick () is naughty
.Select () will trigger listening
5 - Use descriptive variable names
Descriptive names and structures in your code help make comments unnecessary.
This way your code will be much clearer and more efficient:
Option Explicit
Application.ScreenUpdating = False
'It better to switch off properties from starting of your macro
Dim wb as Workbook, ws as Worksheet
Dim Lr As Long
Dim newLr As Long
Dim sht as String, btn As String
Dim lim as Range, rng as Range 'Using these ones directly as a Range is better idea.
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
Lr = ws.Range("B" & Rows.Count).End(xlUp).Row 'Searching last row in column A
'==> if you would like to count rows in A, then change your code. Now it looking for "B".
newLr = Lr + 1
set lim = ws.Range("B" & newLr & ":" + "D" & newLr)
set rng = ws.Range("A" & newLr)
ws.Rows(Lr).Copy
ws.Rows(newLr).Insert
'Range(lim).ClearContents
sht = ws.Name
btn = "vaalidate" & newLr
With Sheets(sht)
.Shapes("validate").Copy
.rng.Paste
.Shapes(.Shapes.Count).Name = btn
End With
Application.ScreenUpdating = True
source to share
It is not necessary to use Sheets(sht).Activate
and Sheets(sht).Range(rng).Select
, it only slows down your code at runtime, instead use completely Shapes
and Worksheets
, for example, the code below:
With Sheets(sht)
.Shapes("validate").Copy
.Paste
.Shapes(.Shapes.Count).Name = btn
End With
source to share