We help IT Professionals succeed at work.

USER FORM TO INPUT DATA WHEN DOUBLE CLICK ON A CELL IN EXCEL

614 Views
Last Modified: 2017-03-30
WHAT I WANT IS WHEN YOU DOUBLE CLICK ON A CELL  IN THIS CASE A PN IN CELL B10 A USER FORM APPEARS TO ENTER INFORMATION FOR THAT GIVEN CELL.  I WANTED TO DO THIS ACROSS MULTIPLE CELLS  FROM B10:B19.

ATTACHED IS AN ATTACHMENT ILLUSTRATING WHAT I WANTED TO DO WITH AN EXAMPLE OF HOW THE USER FORM SHOULD LOOK LIKE.  VERY SIMPLE FORM.
C--Users-lfreund-Desktop-INPUT.xlsx
Comment
Watch Question

NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Does the attached file get you started?
C--Users-lfreund-Desktop-INPUT-with.xlsm

Author

Commented:
It's a great start........"AWESOME" but I have three questions.

1.   Is there a way that the cell that I double click shows up on the userform so we know what PN we are seeing?
2. The data that gets entered for each PN can it be seen every time it's being clicked on and not disappear when you exit.
3.  The OK button on the userform does not work....
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
1 That can easily be done, where would you want it to appear? In a label?  As part of the userform caption.

2 You would need to store the data somewhere, eg a worksheet. I didn't include that part because I don't know where/how you would want it stored. If the data was stored somewhere we could use the userform's Initialize event to load up the relevant data for the PN that's been clicked.

3 Again, didn't do anything with that as I don't know what you would want to do. MInd you, that is where the code for 2 would probably go.

Author

Commented:
1.  It would be great on the Userform caption or Label; whatever is easier.

2.  The data can be stored on Sheet4 for example.  It does not matter how it's stored as long as I click on the PN and the information I typed is there.

3. I figured this out.....I just use UserForm2.Hide for my OK button.
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
How would you want the data stored?

Author

Commented:
Any format.  All I really care is that it's visible when I click on a particular PN. So the user enters the information for that PN and closes the file.  Then later anyone can go back in it and see what was entered by double clicking on the pn to see the information.....
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Well I've come up with something, but to be honest It's not the way I would store the data.

Anyway, take a look at the attached file.
C--Users-lfreund-Desktop-INPUT-with.xlsm

Author

Commented:
Like it.....but once I click away to another PN and go back the data disappears.  What would you recommend.....I'm certainly not picky and open to anything.   You are awesome......
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
What do you mean by 'click away to another PN'?

When the userform is displayed you can't click on another PN, or anywhere on the worksheet for that matter.

Author

Commented:
Sorry about the confusion.  So for each PN the user will enter information for that particular one.  I guess if I was to close the file and reopen it I wanted to click on any pn and see what was entered.
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
That's what should be happening.

When you double click on a PN the code will search Sheet2 for that PN and if it's found it will populate the userform with the data that's stored for that PN.

If there's no data stored for a PN you'll be presented with a blank form.

Also, when you close the userform via the OK button another search will be done for the PN and if it's found the data for that PN will be updated with the data from the userform.

If the PN isn't found then a new row of data will be added for the PN.

Note, if you click Cancel the userform will just be unloaded without any data being saved/changed.

Author

Commented:
It's not doing that.....not all PN information is being saved.
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
It's working for me, did you change anything?

Author

Commented:
No.   Can you please reattach the file that works for you.
Analyst Assistant
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
.

Author

Commented:
ABOSLUTELY AWESOME!!!!!  THANK YOU VERY MUCH! YOU ARE THE BEST!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.