Conditional activation of macro button Solved/Closed

Question

Hello,

I was wondering if it is possible to only make a button work if there are only two cells selected.

I have got a button which will copy the selected data at the bottom of the list on the other sheets.
People are supposed to select two cells, A10 and B10 for example.
Apparently some people working with this file select more cells, A10, B10, C10 for example, by mistake.
This will mess up the rest of the sheets.

I was wondering if this could be prevented, so that the button won't work unless there are two cells selected.

Best regards,
Trowa

venkat1926 · Answer

try this.  

right click the sheet tab and click "view code" 
in the resuting window copy paste this code 
confirm whether it works. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$10:$B$10" Then
<write you macro name>
End If
End Sub

venkat1926 · Answer

mine also is only an example 
if neessary modify the code something like this 

change 
"$A$10:$B$10" to 
"a100:B200"

Trowa · Answer

I don't know how to implement the code.

To activate the button I already have a private sub:

Private Sub ND_Click()
    BeveiligingOpheffen (Macro name)
    NamenDoorvoeren (Macro name)
    CellenSplitsen (Macro name)
    AlfabetischSorterenMedewerkers (Macro name)
    CellenSamenvoegen (Macro name)
    MaandbladenAlfabetischSorteren (Macro name)
    BeveiligingHerinstellen (Macro name)
End Sub

I tried to add the code as an additional Private sub among other things, but that doesn't work.

What do I need to do to make it work?

venkat1926 · Answer

I presume those seven macros are in the standarad module.

then try this (I have not checked)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$10:$B$10" Then
BeveiligingOpheffen
NamenDoorvoeren
CellenSplitsen
AlfabetischSorterenMedewerkers
CellenSamenvoegen
MaandbladenAlfabetischSorteren
BeveiligingHerinstellen
End If
End Sub

Excelguru · Answer

just use 
selection.cells.count=2

venkat1926 · Answer

Your problem is not clear. 

if you have a button you must assign a macro to that button or write a code in the button_click code 

Have a small macro in standard module like this 
Sub test()
Dim r As String
Worksheets("sheet1").Activate
range("a1").select
r = InputBox("type the range you want to select  e.g a10:b10 ")
Range(r).Select
End Sub

assign this macro "test" to your button
so when you click the button the "test" will run and ask you the range to be selected 
type 
A10:B10
and click ok
now A10:B10 will selected and so the event code will run.

I hope I am clear to you.

venkat1926 · Answer

as I do not have all your macro codes I cannot check completely 

now you modify you button click codes like this (i assume the main data base is sheet 1 and if necessary modify this line)

Private Sub ND_click (ByVal Target As Range)
Worksheets("sheet1").Activate
Range(Cells(Selection.Row, "a"), Cells(Selection.Row, "b")).Select
'the above two lines  select A and B column only whatever the person selects
'But he has to select at least one cell.
BeveiligingOpheffen
NamenDoorvoeren
CellenSplitsen
AlfabetischSorterenMedewerkers
CellenSamenvoegen
MaandbladenAlfabetischSorteren
BeveiligingHerinstellen

End Sub 


Ty the above code. there are comments on the 4th and 5 th line . 
these lines can be deleted once you get the hang of it. 

If this is ok post confirmation.

Excelguru · Answer

as per your first query
"I was wondering if it is possible to only make a button work if there are only two cells selected."

you can include the following line at first in the button click event you can do this
If selection.cells.count<>2 then exit sub

Trowa · Answer

Thanks Venkat,

The third line of code was the one I was looking for.

If I implement the full Private Sub it gets stuck on the first line.
After trying a few things the preferred result was achieved by deleting this part: "ByVal Target As Range".
I don't know what it does for the code (tried to use help on ByVal, but didn't get any wiser), but apparently it wasn't necessary.
Since the button is on the data base sheet I also deleted the 2nd line of code.

Now it doesn't matter how many cells they select, since only the cells from column A and B will be used for the code. Great!

Kind regards,
Trowa

Trowa · Answer

Thanks Excelguru for your reply,

My first post wasn't very accurate.

I noticed you changed the "=" from your first post into "<>" in your latest post.
What does "<>" do, since help doesn't provide any info.

Kind regards,
Trowa

Excelguru · Answer

Hi

<> means "Not equal to"

In my first post I meant this

sub yourbuttonevent()
If selection.cells.count=2 then
your code here
End if
End sub

My 2nd post has the same logic but the only diff is

sub yourbuttonevent()
If selection.cells.count<>2 then exit sub
your code here
End sub

Hope you are clear

venkat1926 · Answer

I presume your problem is solved.  "By val target as range" is appended normally  in an event code (that is when you run some codes when some event occurs on the sheet or in the workbooks for e.g  when you change the entries in a cell or when you move from one cell to another and when you activate a sheet or when you open a waorkbooks or close a workbook etc.).  These are not in standard modules but in either sheet moduel or workbook module.

Conditional activation of macro button

12 responses

Subscribe To Our Newsletter!