CheckBox code, excel

Student1000

Board Regular
Joined
Jul 17, 2007
Messages
58
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi Student1000,

Try this:

Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Hi again Student1000,

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

Code:
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. :oops:

Keep Excelling.

Damon
 
Upvote 0
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 :(
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
OK...I got it :biggrin:

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.
 
Upvote 0

Forum statistics

Threads
1,216,496
Messages
6,130,983
Members
449,611
Latest member
Bushra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top