CheckBox code, excel

[Student1000]

Ok, i have created checkbox(es) that i want it to add values to cells upon clicking. Once selected (checked), the option(value) should be populated on the next available row in column D within (D5:D20) range.
Uncheck to clear.The value will be a sentence like "Thank you for choosing our services, we appr your busin"

Here is what i can come up with which i know does not make much sense. Thanks

Private Sub CheckBox1_Click()

Application.WorksheetFunction.CountA (Range("D:D")) + 1
If CheckBox1 = 1 Then Application.Goto Sheet1.Range("D5:20")
& Cells(NextRow, 2) = "Thank you for choosing our services, we appr your busin"
Elseif activesheet.range().value = Null
NextRow =Application WorksheetFunction.CountA(Range("D:D"))+1
End Sub

 

[Damon Ostrander]

Hi Student1000,

Try this:

Private Sub CheckBox1_Click() 

   If CheckBox1 = True Then 

         Dim NextCell      As Range
         'Set NextCell to last unavailable cell beyond D4 (next cell is available) 
         Set NextCell = Sheet1.Range("D4").End(xlDown)
         'Quit if no place to put value (next available cell is beyond row 20)
         If NextCell.Row < 32000 AND NextCell.Row > 20 Then Exit Sub
         NextCell.Offset(1).Value = "Thank you for choosing our services. We appreciate your business."

   End If            

End Sub

Damon

 

[Student1000]

Thanks, Damon Ostrander

At first it gave me an error "Runtime error 1004: Application-defined or Object-defined error"
But i went ahead and add some values within the range(D4:D20) and it works, i guess the range can not be empty....

Thanks again for your help.

 

[adv_uk]

looks simple enough, but cant get it to work

hi,

I regularly read posts on this forum and experiment with posts in my own spreadsheets in an effort to learn more and more about excel.

I'm not a complete novice, and the post above seems simple enough... although it doesnt do anything in my sheet.

I've checked the range D4:D20 (i've left the last few blank), sheet name (sheet1), check box name (CheckBox1), etc.. could there be anything obvious i'm missing?

thanks in advance,
Alex

 

[Student1000]

Mhhh!!....i dont understand that either. I had such i problem at the begining but i dont remember doing anything more than adding values to the first two/three cells. I have the exact names !!???
I want to learn more about this too, so i will attempt it again and see what i can find.

 

[Damon Ostrander]

Hi again Student1000,

Here is an update to my code that I believe solves the problems:

Private Sub CheckBox1_Click()

   If CheckBox1 = True Then

         Dim NextCell      As Range
         'Set NextCell to last unavailable cell beyond D4 (next cell is available)
         Set NextCell = Sheet1.Range("D4").End(xlDown)
         'Quit if no place to put value (next available cell is beyond row 20)
         If NextCell.Row < 32000 And NextCell.Row > 19 Then Exit Sub
         If NextCell.Row > 32000 Then Set NextCell = Range("D4")
            
         NextCell.Offset(1).Value = "Thank you for choosing our services. We appreciate your business."

   End If

End Sub

I learned once again never to post code that I haven't actually tested myself.

Keep Excelling.

Damon

 

[Student1000]

Yes that works but i 've also noticed something (didnt know before), that the check box actually add values to the first two cells only, thats why if you have data in the fist two cells it will either replace or do nothing...

Therefore, if you have multiple checkboxes(i created for experiment) that add to a column(D2:D20), each checkbox will only add to a certain specified row, and if that row is occupied it will not go to the next available cell.
That is my assumption after testing the code (using multiple checkboxes).
I am not an expert, so let me know if am fooling my self

 

[Damon Ostrander]

Hi again Student,

I believe the problem is that I assumed there was some sort of header in cell D4. If you don't have or want a header in D4 I suggest you simply enter a single space character in the cell. The two-cell limit should then go away.

Damon

 

[Student1000]

Alright, I know i probably sound like an idiot by now but i just have to ask this. For some reason, the code above works perfectly on a new workbook but when i insert in my existing workbook it gives me the blues :x The range is (B42:B60)
I put a heading in B42 but it only populates B43. Again, It works very well on a new workbook but not on my existing one.

 

[Student1000]

OK...I got it

The reason is that i was changing the ranges without changing the available cells(lack of a better word). ie. if want to populate (B42:B60), i cannot have 19 as next available cell, it has to be more than that....
In my case i put 70
It works :wink:
Thanks Damon.