Conditional activation of macro button [Solved/Closed]

Trowa - Sep 25, 2009 at 07:37 AM - Latest reply:  Trowa
- Oct 6, 2009 at 06:35 AM
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
See more 

17 replies

Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Oct 2, 2009 at 08:49 PM
1
Thank you
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.

Thank you, venkat1926 1

Something to say? Add comment

CCM has helped 1679 users this month

venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Sep 25, 2009 at 10:18 PM
0
Thank you
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
Thanks for your reply Venkat, but the mentioned range was an example. The selected range people are supposed to select can be anything from A8:B8 untill A200:B200.

Maybe if the above mentioned range is hard to implement, you can say that when a cell in column C is selected the button won't work.

Can you help me out with this?

Best regards,
Trowa
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Sep 28, 2009 at 08:10 PM
0
Thank you
mine also is only an example
if neessary modify the code something like this

change
"$A$10:$B$10" to
"a100:B200"
0
Thank you
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 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Sep 29, 2009 at 08:25 PM
0
Thank you
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 261 Posts Saturday April 11, 2009Registration date June 21, 2011 Last seen - Oct 1, 2009 at 06:19 AM
0
Thank you
just use
selection.cells.count=2
Thanks guys for the help, but I still can't get it to work.

Venkat's solution will make the macro's work when I select the given range but I want them to work when I click the button. So when I click the button, check if the given range is selected, if true then proceed activating the macro's, if not do nothing.

With Excelguru's solution I keep getting error 450, incorrect number of arguments or invalid property allocation (this is my attempt to translate the error message). I tried to put the line of code as the first line in the private sub and as the first line of the first macro in the module, but the result is the same; error 450.

I thought to be smart and tried:

Private Sub ND_click (ByVal Target As Range)
If Target.Address = "$A$10:$B$10" Then
BeveiligingOpheffen
NamenDoorvoeren
CellenSplitsen
AlfabetischSorterenMedewerkers
CellenSamenvoegen
MaandbladenAlfabetischSorteren
BeveiligingHerinstellen
End If
End Sub

But this doesn't work as well.

What else can I do?
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Oct 1, 2009 at 09:31 AM
0
Thank you
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.
I can't thank you enough for the time your taking to help me.

What I want is to create a foolproof situation, since other people are working with the file I created.

On the first sheet I have got a list of names and their functions of the company.
Column A has the surname, column B has the forename and column C has their function.
The surname and the forename, but not their functions, are also on the twelve other sheets.
That's why I made a button to copy/paste the names to the other sheets and put them in alphabetical order.
I made a manual telling the people to select only the surname and forename of the employees that they want to copy/paste before clicking the button.
For some reason they also (sometimes) select their function and when they hit the button, the sheets the names are pasted to are messed up. Because in the twelve destination sheets I have different information in column C.

That's why I was looking for a way so that the button will only work when the surname and the forname of an employee is selected and not the function.

In the last solution you have provided, people using the file, will have to check in which cells the names are and then input the cell range. This could cause another problem, since the people using the file are not the brightest ones. They could also input for example the range A10:C10.

Idealy what I want is this:
When the surname and the forename are selected (e.g. A10:B10) and the button is clicked, the macro's will work.
When more data is selected (e.g. A10:C10), the button won't work and a message will appear telling the user to only select the surname and forename of the employee they want to copy/paste.

I hope my problem is clear now and my apologies for any confusion I have caused.

Kind regards,
Trowa
Excelguru 261 Posts Saturday April 11, 2009Registration date June 21, 2011 Last seen - Oct 3, 2009 at 06:38 AM
0
Thank you
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
0
Thank you
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
0
Thank you
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 261 Posts Saturday April 11, 2009Registration date June 21, 2011 Last seen - Oct 5, 2009 at 08:57 AM
0
Thank you
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
Thank for your explanation Excelguru, good to know.

Kind regards,
Trowa
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Oct 5, 2009 at 07:56 PM
0
Thank you
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.
Just to be complete: My problem has been solved.

Thanks for the help.