Offset Positions When Unloading Userform

[liam_bettinson]

Hi guys, apologies for the length of this description but I felt it necessary to get all (or most) of the info over...

As per my other thread today, I am putting together a labour workload sheet that is laid out in a grid of dates along the top and personnell names down the left. I have a VBA code set up within the grid (shown below) so that on double clicking any cell within this grid, three msgboxes in total appear (one after another following data entry). The first msgbox is the 'site reference', which can be multiple entries up to a total of approx 12, all seperated by commas. The second msgbox is for the company job number, which again can be multiple entries and the third box is the hours spent on site, again multpile entries can be made.

I can't attach sheet due to filesize even when compressed, however my sheet is laid out so that out of every 3 rows within the grid, the two lowest rows are set to row height zero (effectively hiding them from view) and the code takes the data from the last 2 mgsboxes and inserts it into these hidden rows/cells directly below the active cell (data from first msgbox is inserted into current active cell). This hidden data is then viewed via a completely seperate piece of code that references these hidden cells in addition to the active cell in another section of the sheet. Please see code below which works fine when initially entering data:

Please Login or Register  to view this content.

My issue with this code is if the data entered needs to be edited at a later date, you must double click the cell again and re-enter everything! Is it possible to create some sort of message box so that when double clicking the cell the msgboxes/forms that pop up still contain the previously entered data that can simply be edited rather than having to re-enter everthing and potentially missing something off?

Any pointers would be greatly appreciated! Thanks in advance! Any more info needed please just ask...

[snb]

Please mark your previous thread as 'solved'

And dive into the world of VBA 'userforms'.

[liam_bettinson]

Please mark your previous thread as 'solved'

And dive into the world of VBA 'userforms'.

Apologies for being a complete 'noob' to the forum but how do I mark it as solved? I'll check out userforms yeah, any advice on them before I delve??

[snb]

You can click in my signature for a 'manual' to mark your threads as 'solved'.

No one will blame you for reading the forum rules , please do.

[liam_bettinson]

You can click in my signature for a 'manual' to mark your threads as 'solved'.

No one will blame you for reading the forum rules , please do.

Done! Thanks hehe

[snb]

You overlooked in the forum rules: please do not quote.....

[liam_bettinson]

My apologies, I should have read and will do now

Regarding the topic of the thread, I've looked into userforms and have created one that pops up upon double clicking a cell on the spreadsheet and contains 3 text entry boxes and a 'done' command button (grid area not actually specified, would like to limit this to D8:BNW900 - any ideas how to edit the below code?)

Please Login or Register  to view this content.

The issue I have now is how do I transfer the entered info into a specific area of my grid rather than a generic 'database'? I wish for the first text box data to go into the current active cell, 2nd txt box data into the cell directly below active cell (offset 1, 0) and 3rd box data into 2 cells directly below active cell (offset 2, 0). Any ideas on the code I need?

Also can anybody advise that following entry of data into the userform, if I double click that cell again to edit the info, will this information still be in the userform for editing? If not how do I achieve this?

Any more info required please ask...

[liam_bettinson]

Can anyone help?

[Kyle123]

To limit the range, something like:

Please Login or Register  to view this content.

To put the text into the cells, something like:

Please Login or Register  to view this content.

[liam_bettinson]

Would those 2 pieces of code be mixed together or in seperate subs? Any chance of a full description? Thanks for the help!!

[tigeravatar]

liam_bettinson,

See attached example workbook based on criteria you've described. Doubleclicking in sheet1, range D8:BNW900 will open the userform. If there is already data in the cell clicked (and/or the two cells to its right), the data will be pulled into the userform. Clicking Done will output the textbox values into the cell that was doubleclicked and the two cells to its right. Let me know if that is what you were looking for

Hope that helps,
~tigeravatar

[liam_bettinson]

Tigeravatar, that works great! THanks so much! However I want it to paste the values entered in each text box vertically rather than horizontally, ie all in the same column directly underneath each other on 3 different rows, can this accommodate that?

Also it has now stopped my other piece of code working that allowed you to view the held data by doing a SINGLE click. Code as follows, please advise if this needs to be changed as it worked ok before entering the code on your spreadsheet:

Please Login or Register  to view this content.

[tigeravatar]

liam_bettinson,

Updated example workbook to pull information vertically. Also added the Worksheet_SelectionChange event. I experienced no issues with it. It doesn't auto-update when the userform closes because there's not a selection going on. just click into a cell and it will update. If preferred, the C2, C4, C6 cells can be updated with the userform's submit button as well to keep things seamless.

Hope that helps,
~tigeravatar

EDIT: Updated attachment to correct one

[liam_bettinson]

Sorry buddy but this is still pasting in the data within one row rather than on 3 rows in a single column?

Also I've copied the code into my own workbook/VBA and it doesnt seem to work The Userform appears and I can enter the data into the text boxes but when I click the 'Done' command button it does not close and hence does not update my spreadsheet. I've changed all the userform, textbox and button names to match yours exactly, the only properties that are different are the physical text box sizes and fonts but wouldnt imagine that would make a difference would it?! Anything else I'm missing that will prevent it from working?

[tigeravatar]

Edited recent post to have correct attachment, sorry about that

[liam_bettinson]

No problem my good man. Right...think we're getting near, the example you sent works great but for some reason on my own form it's not working. It all works as it should up until I click 'done' and nothing happens! I'll triple check the names and properties but I'm pretty sure they all match up to what your's a named, anything else I could be missing?

[tigeravatar]

liam_bettinson,

As long as the control names get updated in your code it should be fine (example: instead of "Me.txt_Data1.Text" it should be "Me.ActualNameOfYourTextControl.Text"). If its still giving you an issue, just post your btn_Done_Click() equivalent code and I'll take a look at it.

[liam_bettinson]

Cancel my last post, I neglected to copy and paste the code from within the buttons/form! Thanks so much for your help Tigeravatar, greatly appreciated!! Form works a treat now

[liam_bettinson]

Hi again, apologies to dredge this up, but my boss now wants a couple of changes to the sheet, details as follows:

Instead of the userform being unloaded in the active cell and the two cells directly below the active cell, this now needs to unload in the cell directly below active cell and the two cells under that (basically everything is shifted down 1 cell from the active cell). I've tried to change the following code which is within the userform to change the offset references at the bottom to suit but the position of unloading does not change. I assume something around the 'Unload me' part needs altering? Any ideas?

Please Login or Register  to view this content.

[tigeravatar]

Please Login or Register  to view this content.

[liam_bettinson]

You sir are a true gent! Many thanks once again!!