SAME MACRO DON’T WORK ON (ActiveX Control) BUTTON BUT WORKS ON (From Control) BUTTON

[K]

Hi all, I have macro (see below) in my Sheet Module

MACRO ON (ActiveX Control) BUTTON
**********************************************
Private Sub CommandButton3_Click()
ActiveSheet.Select
ActiveSheet.Copy After:=Sheets(1)
Range("C21").Select
Range(Cells(21, 3), Cells(Cells(Rows.Count, "B").End(xlUp).Row,
"N")).ClearContents
Dim StartRow As Long
StartRow = Cells(Rows.Count, 2).End(xlUp).Row
If StartRow >= 33 Then
Range(Cells(StartRow, 1), Cells(33, 1)).EntireRow.Delete
End If
End Sub

The problem is that when I put this Macro in Sheet Module and put on
"Command Button (ActiveX Control)" (as shown above) it highlights line
"Range ("C21").Select" and don’t work but if I delete that line it
still don’t work. But for some reason when I put this code on normal
Module (as shown below) and set on "Button (From Control)" it works
perfect. i want to put this macro in Sheet Module and want to set it
on "Command Button (ActiveX Control)" and want it to work. Can any
friend have any idea what should i do or what am i doing wron. Any
help will be much appricated

MACRO ON (From Control) BUTTON
*********************************************
Sub AddSht ()
ActiveSheet.Select
ActiveSheet.Copy After:=Sheets(1)
Range("C21").Select
Range(Cells(21, 3), Cells(Cells(Rows.Count, "B").End(xlUp).Row,
"N")).ClearContents
Dim StartRow As Long
StartRow = Cells(Rows.Count, 2).End(xlUp).Row
If StartRow >= 33 Then
Range(Cells(StartRow, 1), Cells(33, 1)).EntireRow.Delete
End If
End Sub

 

[Harald Staff]

Hi

Range("C21") -and Cells and many other things- refers to Me (the sheet with
the code) C21 when in a sheet module, but to activesheet C21 when in a
standard module.
"Don't work" is a pretty general statement, as there are at least four
thousand three hundred ways code doesn't work, but I hope this get you
started. BTW you could of course call code in a standard module from the
sheet module like

Private Sub CommandButton3_Click()
Call Macro1
End Sub

HTH. Best wishes Harald


Hi all, I have macro (see below) in my Sheet Module

MACRO ON (ActiveX Control) BUTTON
**********************************************
Private Sub CommandButton3_Click()
ActiveSheet.Select
ActiveSheet.Copy After:=Sheets(1)
Range("C21").Select
Range(Cells(21, 3), Cells(Cells(Rows.Count, "B").End(xlUp).Row,
"N")).ClearContents
Dim StartRow As Long
StartRow = Cells(Rows.Count, 2).End(xlUp).Row
If StartRow >= 33 Then
Range(Cells(StartRow, 1), Cells(33, 1)).EntireRow.Delete
End If
End Sub

The problem is that when I put this Macro in Sheet Module and put on
"Command Button (ActiveX Control)" (as shown above) it highlights line
"Range ("C21").Select" and don’t work but if I delete that line it
still don’t work. But for some reason when I put this code on normal
Module (as shown below) and set on "Button (From Control)" it works
perfect. i want to put this macro in Sheet Module and want to set it
on "Command Button (ActiveX Control)" and want it to work. Can any
friend have any idea what should i do or what am i doing wron. Any
help will be much appricated

MACRO ON (From Control) BUTTON
*********************************************
Sub AddSht ()
ActiveSheet.Select
ActiveSheet.Copy After:=Sheets(1)
Range("C21").Select
Range(Cells(21, 3), Cells(Cells(Rows.Count, "B").End(xlUp).Row,
"N")).ClearContents
Dim StartRow As Long
StartRow = Cells(Rows.Count, 2).End(xlUp).Row
If StartRow >= 33 Then
Range(Cells(StartRow, 1), Cells(33, 1)).EntireRow.Delete
End If
End Sub

 

[K]

Hi

Range("C21") -and Cells and many other things- refers to Me (the sheet with
the code) C21 when in a sheet module, but to activesheet C21 when in a
standard module.
"Don't work" is a pretty general statement, as there are at least four
thousand three hundred ways code doesn't work, but I hope this get you
started. BTW you could of course call code in a standard module from the
sheet module like

Private Sub CommandButton3_Click()
Call Macro1
End Sub

HTH. Best wishes Harald


Hi all,  I have macro (see below) in my Sheet Module

MACRO ON (ActiveX Control) BUTTON
**********************************************
Private Sub CommandButton3_Click()
ActiveSheet.Select
ActiveSheet.Copy After:=Sheets(1)
Range("C21").Select
Range(Cells(21, 3), Cells(Cells(Rows.Count, "B").End(xlUp).Row,
"N")).ClearContents
Dim StartRow As Long
StartRow = Cells(Rows.Count, 2).End(xlUp).Row
If StartRow >= 33 Then
Range(Cells(StartRow, 1), Cells(33, 1)).EntireRow.Delete
End If
End Sub

The problem is that when I put this Macro in Sheet Module and put on
"Command Button (ActiveX Control)" (as shown above) it highlights line
"Range ("C21").Select" and don’t work but if I delete that line it
still don’t work.  But for some reason when I put this code on normal
Module (as shown below) and set on "Button (From Control)" it works
perfect.  i want to put this macro in Sheet Module and want to set it
on "Command Button (ActiveX Control)" and want it to work. Can any
friend have any idea what should i do or what am i doing wron.  Any
help will be much appricated

MACRO ON (From Control) BUTTON
*********************************************
Sub AddSht ()
ActiveSheet.Select
ActiveSheet.Copy After:=Sheets(1)
Range("C21").Select
Range(Cells(21, 3), Cells(Cells(Rows.Count, "B").End(xlUp).Row,
"N")).ClearContents
Dim StartRow As Long
StartRow = Cells(Rows.Count, 2).End(xlUp).Row
If StartRow >= 33 Then
Range(Cells(StartRow, 1), Cells(33, 1)).EntireRow.Delete
End If
End Sub

Thanks for replying Harald, so if i want this code to be Sheet Module
then how should i be writting it. I want to copy Activesheet then
want to do stuff what its saying in macro. I already know the "Call
Macro1" procedure but can you help how the code should be if i want it
in Sheet module.

 

[Harald Staff]

Thanks for replying Harald, so if i want this code to be Sheet Module
then how should i be writting it.

SOmething like

Private Sub CommandButton1_Click()
Dim oSht As Worksheet
Me.Copy after:=Me
DoEvents

Set oSht = ActiveSheet
DoEvents
oSht.Activate
DoEvents
'from here the oSht. addressing is the big thing:
oSht.Range("C21").Select
oSht.Range("C20").Value = "I am a copy"

End Sub

You take it from here ;-)
Best wishes Harald