How to Enter data and Function in same cell

[tyjb007]

Hello, I am not sure if this should go in programming, so I'm going
to put it in misc. It's for people who might have also had this type
of problem. I had trouble with this and couldn't find it anywhere,
luckily I had a teacher help me with it and it wasn't hard at all.
Here's what the problem was.

My boss, who owns a cabinetry factory, wanted to make a spreadsheet for
customers which they could find out about what the total price would be
for there order. So for the dimensions he wanted them to automatically
round up.

EX: say you typed 8.001, it would round to 9.

There is nothing in excel that does that, without have to use a
function. Well he wanted them to type it in that cell and then have it
change.

So here's how you can have a function in the same cell you need to
type data in. It involves a little bit of VBA.

-Open the excel workbook
-Alt+F11 (This brings open the VBA editor) or go to: Tools / Macro /
Visual Basic Editor
-With the VBA editor open, there will be the Project menu on the left,
and it will have the workbook, Microsoft Excel Objects, and then all
the worksheets.

**Note** All the worksheets should be showing at start, but if they are
not then Go to the Projects menu on the left, hit the minus ( - ) next
to the VBAProject(Workbookname), then hit the minus on the Microsoft
Excel Objects folder, it will then display all the worksheets in the
workbook

-Double Click on the worksheet you wish to edit.
-A window will popup in the work area.
-On the top of that window there are 2 drop-down menus which should
say: (General) and (Declerations)
-Click the one the says (General) and change it to Worksheet
-Now go the other drop-down to the right (I believe it should now
display SelectChange as default). Click on it and select Change.
**Note** there are other things that you can do with it but for the
example I'm giving, I'm going to be using Change, which will
perform the action after I enter data into the cell

In the work area of the window you should see code that looks like
this:


Private Sub Worksheet_Change(ByVal Target As Range)

End Sub




For my example I need to round up the numbers in column C and E, as
they are being enterd, to the next whole Number. So my code looks like
this:


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 3 Or Target.Column = 5 Then
Target = WorksheetFunction.RoundUp(Target, 0)
End If

End Sub




-Target is the default variable for the active cell
-The if statement checks if the changes being made are in column 3
(aka: column C) or column 5 (aka: column E)
-if the user has entered data in either one of those columns then it
will perform the task inside which sets Target (aka: the cell the user
just entered data into) equal to the worksheet function Roundup. Then
the arguments needed for Roundup to function properly, which in the
case for RoundUp would be (number to round up, to which decimal place
- 0 being no decimal places)

I hope this was clear enough and not to confusing. This can be very
helpful if you need/want a function to take place in the same cell you
type data in. This is a way to get around being able to only type in a
function or data into a single cell. Well I hope this helps someone
out. I know that I searched all over the internet and couldn't find
anything.

 

[Don Guillett]

OR without worksheet functions

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 3 Or Target.Column = 5 Then
Target = Int(Target + 0.99999999)
'Target = WorksheetFunction.RoundUp(Target, 0)
End If
Application.EnableEvents = True
End Sub

 

[Guest]

The company is rounding 8.0001 to 9, for costing?? Please give me their name
so I can avoid buying there.

 

[tyjb007]

@Ricter

HaHa, well it never really gets that low. It's for door lengths, so
they will usually enter a fraction such as 3/8. But he needed it
becuase he would otherwise lose like .20 a door, which can add up
pretty fast.