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.
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)
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.
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.
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
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
Dim r As String
r = InputBox("type the range you want to select e.g a10:b10 ")
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
and click ok
now A10:B10 will selected and so the event code will run.
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!
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.