Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Box Value
Hi All
This will be easy for you experts out there. On a UserForm I have a Check Box that when I click on it I want it to add a value of £1 to a Cell on a particular worksheet see code below. Private Sub Add2_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Account") 'find first empty row in database iRow = ws.Cells(Rows.Count, 2) _ .End(xlUp).Offset(1, 0).Row 'copy the data to the database ws.Cells(iRow, 1).Value = Me.Tb5.Value ws.Cells(iRow, 2).Value = Me.Tb3.Value ws.Cells(iRow, 4).Value = Me.Tb4.Value 'ws.Cells(iRow, 3).Value = Me.CHECKBOX4???????? 'clear the data Me.Tb3.Value = "" Me.Tb4.Value = "" Me.Tb5.Value = "" Me.Tb6.Value = "" Com3.Value = "" End Sub Where the ????? are what code would I add to make this work -- Many thanks hazel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Box Value
'ws.Cells(iRow, 3).Value = Me.CHECKBOX4????????
This line of code will not work since the Checkbox is boolean value, either 0 or 1, true or false. If you click the checkbox to add a value to cells(iRow, 3), the value has to be specified in the execution of the control's code like: cells(iRow, 3) = 1 (Or "1" depending on your data type designation) or alternatively: Cells(iRow, 3) = Range("$P$3") (for example) Where "P3" has the value of 1 entered. In other words the checkbox is only a trigger and does not retain a Value. "Hazel" wrote: Hi All This will be easy for you experts out there. On a UserForm I have a Check Box that when I click on it I want it to add a value of £1 to a Cell on a particular worksheet see code below. Private Sub Add2_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Account") 'find first empty row in database iRow = ws.Cells(Rows.Count, 2) _ .End(xlUp).Offset(1, 0).Row 'copy the data to the database ws.Cells(iRow, 1).Value = Me.Tb5.Value ws.Cells(iRow, 2).Value = Me.Tb3.Value ws.Cells(iRow, 4).Value = Me.Tb4.Value 'ws.Cells(iRow, 3).Value = Me.CHECKBOX4???????? 'clear the data Me.Tb3.Value = "" Me.Tb4.Value = "" Me.Tb5.Value = "" Me.Tb6.Value = "" Com3.Value = "" End Sub Where the ????? are what code would I add to make this work -- Many thanks hazel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Box Value
if me.Checkbox4.Value then
ws.Cells(iRow, 3).Value = ws.Cells(irow,3) + 1 End if If you mean just enter a 1 (add not literally meaning to sum) if me.Checkbox4.Value then ws.Cells(iRow, 3).Value = 1 ws.Cells(iRow,3).Numberformat = "£ #" End if -- Regards, Tom Ogilvy "Hazel" wrote in message ... Hi All This will be easy for you experts out there. On a UserForm I have a Check Box that when I click on it I want it to add a value of £1 to a Cell on a particular worksheet see code below. Private Sub Add2_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Account") 'find first empty row in database iRow = ws.Cells(Rows.Count, 2) _ .End(xlUp).Offset(1, 0).Row 'copy the data to the database ws.Cells(iRow, 1).Value = Me.Tb5.Value ws.Cells(iRow, 2).Value = Me.Tb3.Value ws.Cells(iRow, 4).Value = Me.Tb4.Value 'ws.Cells(iRow, 3).Value = Me.CHECKBOX4???????? 'clear the data Me.Tb3.Value = "" Me.Tb4.Value = "" Me.Tb5.Value = "" Me.Tb6.Value = "" Com3.Value = "" End Sub Where the ????? are what code would I add to make this work -- Many thanks hazel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Box Value
Hi Tom
Solved my little problem with the last snippet of code, much appreciated -- Many thanks hazel "Tom Ogilvy" wrote: if me.Checkbox4.Value then ws.Cells(iRow, 3).Value = ws.Cells(irow,3) + 1 End if If you mean just enter a 1 (add not literally meaning to sum) if me.Checkbox4.Value then ws.Cells(iRow, 3).Value = 1 ws.Cells(iRow,3).Numberformat = "£ #" End if -- Regards, Tom Ogilvy "Hazel" wrote in message ... Hi All This will be easy for you experts out there. On a UserForm I have a Check Box that when I click on it I want it to add a value of £1 to a Cell on a particular worksheet see code below. Private Sub Add2_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Account") 'find first empty row in database iRow = ws.Cells(Rows.Count, 2) _ .End(xlUp).Offset(1, 0).Row 'copy the data to the database ws.Cells(iRow, 1).Value = Me.Tb5.Value ws.Cells(iRow, 2).Value = Me.Tb3.Value ws.Cells(iRow, 4).Value = Me.Tb4.Value 'ws.Cells(iRow, 3).Value = Me.CHECKBOX4???????? 'clear the data Me.Tb3.Value = "" Me.Tb4.Value = "" Me.Tb5.Value = "" Me.Tb6.Value = "" Com3.Value = "" End Sub Where the ????? are what code would I add to make this work -- Many thanks hazel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
make a check form, and then have info go to a check register | Excel Worksheet Functions | |||
Copy and move check box (check boxes) with new cell link? | Excel Worksheet Functions | |||
Increase size of a Forms Check Box (click on to enter check mark) | Excel Discussion (Misc queries) | |||
Check if Conditional Format is True or False / Check cell Color | Excel Worksheet Functions | |||
Enable check box in protected sheet + group check boxes | Excel Discussion (Misc queries) |