Start Free Trial

asked on

I need a User Form to Input Data appear when a cell is clicked on

I had this question after viewing USER FORM TO INPUT DATA WHEN DOUBLE CLICK ON A CELL IN EXCEL.

I have a similar file but I need a macro that will allow me to have more fields than the normal Excel form allows. I need a form that will accommodate ~100 fields. I would like the User to be able to click a cell with their name or e-mail address and a form pops up for them to input more information, which would populate on the same row as their name.

I think 100 fields is a bit over the top. Do you have a table with 100 Columns in it?

This example will construct a UserForm with as many columns as your data has fields. It's basic but I was just experimenting with a dynamic userform

Roy, you didn't attach your example.

ASKER

Yes, my spreadsheet probably exceeds 100 fields. It's an employee database, storing all sorts of information and access for each employee.

Try again. Thanks Martin
Dynamic-UserForm.xlsm

ASKER

Thank you! A couple of things, I need code which will allow users to click on the row with their names and when it's clicked, a data form pops up, which will populate the data across the fields. Are you able to provide that code? Secondly, your form only has 4 fields, how do you add more fields? As I mentioned, I need over 100 fields..

ASKER

Disregard the 2nd part of that. I just dragged the corners to expand the fields and it automatically adds them to the form. Pretty Cool! I just need the macro at this point. Thanks!

ASKER

Uh, never mind again, I see what this does now. Sorry, it pays to take time to review everything before I respond! This is great, let me fill in my data and test it and I'll be back to you, but looks like this might do the trick!

ASKER

Quick question though, if I needed to tweak the form for certain users so they didn't see all of the fields, just maybe 20 or so, how could I do that, while still allowing all of the 100+ fields to remain on the spreadsheet? Thanks!

ASKER

Ok, I've tested this with real data. When I click "Maintain Client List" and try to input new data into the fields, it doesn't populate the rows. Nothing Changes. Additionally, only the first field appears in the form, none of the other fields are populated in the field. I copied and pasted my data over the columns in your sample, did that have anything to do with it? Any suggestions?

ASKER

On your sample, if I click New or Edit in the Data Form, it adds a new row. I need the information to be entered on the same row.

Can you attach an example of your workbook. The code for the button needs changing, but I need to see how you are using the form.

I woild suggest that you select a cell within an entry and get the line from that cell.

ASKER

Here's a sample. I need the ability to edit existing data on a row of data, not just add new data. Additionally, per my question above, is it possible for the form to display fewer fields on the data form if necessary, without wiping them off of the spreadsheet?
Sample-Form2.xlsm

Now I have an example I'll see what i can do to create something more suitable

Would you be displaying just the first 20 columns - A to T as the alternative?

ASKER

Approximately that many columns, but I'll need it to be flexible so I can adjust as needed.

Just an up date. I have built in a way to select how many fields to display.It needs tidying up but I should be able to post a new form tomorrow.

ASKER

Thanks, looking forward to it! Hoping to finish this up before the weekend!

I've re-designed the original form. The activecell must be within the data before displaying the form.

1. You can now select how many columns to load
2. If the user selects > 20 then a password is required. Password is "secret"
3. There is an Edit Button.
4. If the check box is clicked a new entry can be added. Clear the checkbox and the selected row will be displayed for editing
Sample-Form3.xlsm

ASKER

Thank you! This is awesome! I love how many rows can be displayed! Couple of things:
1) I made sure that the checkbox was not clicked and I still can't edit. I type over what's in the field on the form and it doesn't change the cell in the spreadsheet. I clicked "Done" and it still doesn't change.
2) When I actually click the "Edit" button I get a macro error (see attached file):
Capture.PNG

ASKER

Additionally, when I click the box which is supposed to add a new row, it doesn't add it to the bottom of the spreadsheet. Nothing happens.

I'll check it later, but ir was working fine for me

ASKER

Not sure what the issues is, it appears to be pretty straightforward- can't imagine why it wouldn't work, but I attached the code. Perhaps it's something I need to add to my VBA? Not sure.

ASKER

Seems to be getting hung up here:
Private Sub cmdEdit_Click()
For Each oCtl In Me.Frame1.Controls
If TypeName(oCtl) = "TextBox" Then Cells(lRw, CInt(oCtl.Tag)) = oCtl.Value
Next oCtl
End Sub
End Sub

This works, it edits and adds new. Originally I used to buttons but decided one would do. When I deleted the unused one I left an extra End Sub in, also I've changed a couple of lines

The Done button closes the form
Sample-Form3.xlsm

ASKER

Looks Perfect! I tested it and it seems to work exactly as I envisioned!

This is just a minor thing, if it can't be done, no worries, but if my cursor is in a blank row (not populated with data in the previous row), it says "No Data Detected Please Select a Cell Within the Table of Data". I understand why the "Edit" function needs to be in the appropriate row, but is it possible to allow a row to be added beneath the last populated row regardless of where the cursor is? I'm afraid to ask, because everything else is so perfect now, I don't want that to break that, so if its too much of a headache, just let me know and I'll instruct the users.

The cursor needs to be in the table so that the userform can recognize the address of the data. This could be avoided but the data would always need to start in the same row and the form would be less dynamic.

Another option would be to format the data as a Table then there would be an object to recognise.

If the activecell is not in the data then the code assumes the data starts in A4, the form continues to load and will dispaly the first row of data.
Sample-Form3.01.xlsm

ASKER

I'm not sure I understand your comment, are you saying that you did find a way to do what I needed in this new file? It sure looks that way when I tested it, just want to make sure nothing else changed and I'm understanding fully what this 2nd file does (or not). I will wait to hear from you, but otherwise, I think this might do it.

The userform needs to identify where the data is. That's why originally it required a cell within the data to be selected. I've changed it so that if the cell is not within the data then it will assume the data startes in A4 as in the example.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Perfect, thank you! This is spot on! Love the additions! I also like that the data doesn't have to be in a table format and it still works. Can you tell me how to change the name of the click button that says "Maintain Client List" I couldn't find it in the code.

ASKER

Never mind, I was finally able to click the Developer tab and modify the button, don't know why I couldn't before. Thank you so much again!!!!! Here's your points!

ASKER

This is the best solution ever, thank you so much!!! It was very appropriate and value added to my work!

Pleased to help.