Multi selection CTRL

[Guest_imported]

I do multi cells selection with CTRL; I would like to apply the same procedure on each of the selected cells.
Due to the fact that the procedure excecute several Select Functions (Offset, resize,..),I suppose that I have to find code to keep in memory the different selected Cells (with CTRL)

 

[kevinclark]

Maybe I am mis-understanding but if you are selecting numerous cells with CTRL then you do not want your functions to select cells (You have already done that with CTRL). Maybe adjust your functions to work with what you have already selected ?

Sub Button1_Click()
For Each Cell In Selection
ConditionalColour Range(Cell.Address), Cell.Column
DoResize Range(Cell.Address)
DoOffsets Range(Cell.Address)
Next Cell
End Sub


Function DoResize(selRange As Range)
Range(selRange.Address).ColumnWidth = 15.86
Range(selRange.Address).RowHeight = 44.25
End Function


Function ConditionalColour(selRange As Range, selCol As Integer)
If selCol = 6 Then
selRange.Interior.ColorIndex = 36
End If
End Function

Function DoOffsets(selRange As Range)
Range(selRange.Address).Offset(0, 1).Value = "Right 1 column of selected cell"
End Function

 

[Guest_imported]

In fact, I have short experience in VBA.

I select several cells with CTRL (in the same column)
What I want to to:
1.Deprotect the sheet
2.Put the value of the selected cells in an other column (2 to left side)
3.Reprotect the sheet

 

[kevinclark]

Sub Button1_Click()
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
For Each Cell In Selection
DoOffsets Range(Cell.Address)), Cell.Column
Next Cell
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub


Function DoOffsets(selRange As Range, selCol as Integer)
If selCol >= 3 then
Range(selRange.Address).Offset(0, -2).Value = Range(selRange.Address).Value
End IF
End Function

 

[kevinclark]

Hi Erwin

Just in case the Worksheet is password protected. The method above will require you supply the password in code which means wandering eyes will be able to see the password unless you also protect and hide the code module.

If you need to supply a password then here is another sample:

Sub Button1_Click()
ActiveSheet.Unprotect ("YourPassword&quot
For Each Cell In Selection
DoOffsets Range(Cell.Address)), Cell.Column
Next Cell
ActiveSheet.Protect ("YourPassword&quot
End Sub


Function DoOffsets(selRange As Range, selCol as Integer)
If selCol >= 3 then
Range(selRange.Address).Offset(0, -2).Value = Range(selRange.Address).Value
End IF
End Function

 

[Guest_imported]

I'm sorry, but with my very short VBA experience, I don't understand how I must personnalize:

DoOffsets Range(Cell.Address)), Cell.Column

 

[kevinclark]

Hi Erwin

All you should need to do is cut and paste the code into your module. To be on the safe side and in case you already have a number of buttons on your worksheet then maybe try this:

1) Cut and paste the code into your module (say Module1).
2) Rename the routine called Button1_Click() to Erwin1()
3) Create a new button on your worksheet
4) Right click on the new button and under Assign Macro, assign the button to Erwin1

All you should need to do then is select a few cells and press the button to see if it works for you. Hopefully it will.

 

[Guest_imported]

Thank's so lot for your help;It's now working.There was only one ) to much in your code.

DoOffsets Range(Cell.Address), Cell.Column

 

[kevinclark]

Good catch Erwin