2

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?

6
  • 1
    Are 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.
    – panhandel
    Jul 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. :/
    – Jason
    Jul 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.
    – Jason
    Jul 17, 2014 at 21:18
  • Correct, text box, button, and maybe a label. I'll see about jotting down some sample code
    – panhandel
    Jul 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.
    – Snowbody
    Jul 18, 2014 at 1:07

1 Answer 1

0

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.

4
  • 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.
    – Jason
    Jul 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 :)
    – panhandel
    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. :/
    – Jason
    Jul 17, 2014 at 22:26
  • No worries, accepting it is better!
    – panhandel
    Jul 18, 2014 at 1:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .