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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 

Student1000

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

Board Regular
Joined
Feb 9, 2007
Messages
90
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

Board Regular
Joined
Jul 17, 2007
Messages
58

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 

Student1000

Board Regular
Joined
Jul 17, 2007
Messages
58

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,123,530
Messages
5,602,190
Members
414,513
Latest member
junbuggle

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
Top