Since I can't create a form using VB or ACCESS here at work, I'm trying to figure out if I can use a cell as a makeshift form. I want to label one cell "Enter New Order Quantity." Next to it(next cell), I want the user to be able to input a number, have that number update two other cells, and then have that change stick, so that another number can be entered and added on top of the previous number in the aforementioned cells. Is this possible?
-
1Are you saying a full-fledged VB exe and ACCESS are independently off limits at work? Or are you saying your Excel spreadsheets cannot contain any VBA (VB for Applications) code in them? Because at first look, the two fields you have mentioned, coupled with a VBA-driven button to copy the value around seems like a good way to go.– panhandelJul 17, 2014 at 21:00
-
ACCESS is only off limits because they won't supply it. VB code seems to be off limits because when I try to use it from the dev group, it seems like it's gutted. Everything is greyed out. I can't install anything because no one here has administrator privileges. :/– JasonJul 17, 2014 at 21:05
-
Oh wow! Nevermind. I just realized that I did something on that particular file that won't allow me to use VBA. I created a new file and it's letting me use it. I basically just need a text box and command button, correct? It's been a long time since I've used VB. Could you possibly give me some sample code to do what I want the "Update" button to do? I'm sure I can figure the rest out. EDIT: Basically, I want the update button to take the number from the text box, add it to two separate cells, and then reset the box.– JasonJul 17, 2014 at 21:18
-
Correct, text box, button, and maybe a label. I'll see about jotting down some sample code– panhandelJul 17, 2014 at 21:20
-
1@Jason You have to save the file as .xlsm in order to be able to use VBA. If you save it as .xlsx, all the VBA gets disabled.– SnowbodyJul 18, 2014 at 1:07
1 Answer
Add an ActiveX textbox (Developer tab, Controls section, 'Insert' button. default name is TextBox1).
Add a basic form control button (default name should be Button1)
Select button and click "View Code" in the same Controls section on toolbar
Then the sub should look something like this
Sub Button1_Click()
Sheet1.Range("C1").Value = Sheet1.Range("C1").Value + Sheet1.TextBox1.Text
Sheet1.Range("D1").Value = Sheet1.Range("D1").Value + Sheet1.TextBox1.Text
Sheet1.TextBox1.Text = ""
End Sub
C1 and D1 would be the cells you wanted to copy the quantity to
Edit: Forgot the bit about clearing out the textbox. And on a sidenote, Alt+F11 is a fast way back to the code window.
-
Thanks! However, I would like to add the current value to the value already in the cells. Example: C1 is already 3. Input 2 into the text box. Press the update button. C1 is now 5.– JasonJul 17, 2014 at 21:59
-
OK, easy change, sorry if I missed that before! You will likely want to throw in a check to make sure there's a value in there too, possibly non-negative, because the sample doesn't handle that well :) Jul 17, 2014 at 22:01
-
Thank you so much! I accepted your answer, but it won't let me upvote because I don't have 15 reputation. :/– JasonJul 17, 2014 at 22:26
-