PDA

View Full Version : please help needed using checkboxes & vba!!!



ads_3131
03-05-2011, 05:17 AM
i need some help from a vba wizard for some vba for a check box.....

Basically what i want to do is..... for example... have two checkboxes at the top of a excel sheet, when the first checkbox is ticked i want the contents from lets say cell (E9) put into cell (A4)........ then when the next checkbox is ticked i want the same to happen lets say cell (E10) to then goto the row below (A5).......

ANY HELP PLEASE :(

Bob Phillips
03-05-2011, 05:27 AM
Public Sub Checkbox1_Click()
Range("E9").Copy Range("A4")
End Sub


and so on

ads_3131
03-05-2011, 05:39 AM
Hey thanks thats great but.... lets say theres more checkboxes, either check box can be clicked in any random order......... i would need which ever box is next selected to go onto the row below of the last paste..... :(

otherwise i would have gaps between rows from where the checkboxes are configured...? Could you help please? and thanks again for a fast response....

Bob Phillips
03-05-2011, 02:05 PM
The target is the next row, right? But the source would be checkbox dependent, is that right?

ads_3131
03-06-2011, 04:04 AM
yeah............ any checkbox could be clicked in any random order so starting from a fixed cell (position such as A5) it would need to put in the row below & so on....for each checkbox selected, can this be done?

GTO
03-07-2011, 04:22 AM
Hi there,

Only as Bob appears to be away for a bit, here's a shot... Change ranges/cells to suit of course.

This would go in the worksheet's module:

Option Explicit

Private Sub CheckBox1_Click()
If CheckBox1.Value Then NewDest.Value = Range("D1").Value
End Sub

Private Sub CheckBox2_Click()
If CheckBox2.Value Then NewDest.Value = Range("E1").Value
End Sub

Private Sub CheckBox3_Click()
If CheckBox3.Value Then NewDest.Value = Range("F1").Value
End Sub

Function NewDest() As Range
Set NewDest = Cells(Rows.Count, "A").End(xlUp).Offset(1)
End Function
Hope that helps,

Mark

ads_3131
03-08-2011, 01:04 AM
Cheers! That works great :D ....... how would i configure this to start at a specific row instead of at A:1 ...........? e.g - A17?

Thanks again :)

Bob Phillips
03-08-2011, 01:09 AM
Function NewDest() As Range
Set NewDest = Cells(Rows.Count, "A").End(xlUp).Offset(1)
If NewDest.Row < 17 Then Set NewDest = Range("A17")
End Function