Please help with user form in Excel.

Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

Hello,
I'm getting stumped and just need someone to help out. I know I am over looking something simple but can't figure it out. My user form has numerous checkboxes and textboxes. I have code it to enter the info into a new cell if the box is checked, but the problem is it works fine as long as two check boxes are not checked at the same time.
It is similar to this without all of the names of the checkboxes and textboxes.
Set c = Worksheets("Lot Release Form").Range("a65536").End(xlUp).Offset(1, 0)
If CheckBox1 = True Then
c.Offset.Value = TextBox1.Value
End If
If CheckBox2 = True Then
c.Offset.Value = TextBox2.Value
End If
If CheckBox3 = True Then
c.Offset.Value = TextBox3.Value
End If
If CheckBox4 = True Then
c.Offset.Value = TextBox4.Value
End If

Thank you in advance for your advice

Sincerely,
Jose Perez

2 replies

Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc
AND post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too.


Note: your data need not be to be real data but a good representative of how data looks like
You are overwriting c.Offset.Value if you have 2 checkboxes. So lets say that you have 1 and 3 checked. You set c.Offset.Value to TextBox1.Value, but you're not doing anything with it. Then, since 3 is true, it sets the c.Offset.Value to TextBox3.Value, overwriting the previous value.

After the conditionals, you only get the results from the 3rd checkbox, but not the ones from the 1st one.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
As Sorgens has correctly pointed out that you find cell C and then irrespective of what you did later, you never change location of C
you may want to consider some thing like this

  Set c = Worksheets("Lot Release Form").Range("a65536").End(xlUp)
   If CheckBox1 = True Then
      Set c = c.Offset
      c.Value = TextBox1.Value
   End If
   If CheckBox2 = True Then
      Set c = c.Offset
      c.Value = TextBox2.Value
   End If
   If CheckBox3 = True Then
      Set c = c.Offset
      c.Value = TextBox3.Value
   End If
   If CheckBox4 = True Then
      Set c = c.Offset
      c.Value = TextBox4.Value
   End If