Replies

RG Thanks for that
Did a compile and doesn’t like line “Sheet1.Select” which I changed to read “Monthly Report.Select” and the userform is not populating with the saved data

Phil,

Then in the Workbook_Open event I’d check for this temp sheet and if it exists automatically display the form and load it with the values from the temp worksheet and then delete the temp sheet. This should keep things nice and tidy.

If you want to make your code easier to maintain you could make a subroutine out of the part that saves the data and then just call the subroutine with the sheet to which the data should be saved! One block of code to maintain then if you add or delete fields from the form.

HTH :cheers:

RG
I am having trouble with this bit, checking the sheet exists and then populating the form with the existing data.

Your idea of a separate module looks good too.

RG hi again
The file is set as an xltm so the save function would be xlsx but of course that will not allow further editing. The intention is that staff generate the report monthly and send to Commercial Manager for compilation in to consolidated report.

[*]Yes a partial record
[*]Yes save automatically
[*]Have a Save for Edit button
[*]In the workbook is OK or maybe an external file
[*]Single user workbook

Thanks Maude
No it wasn’t a test I could not disable the start up code using the usual hold the shift key when opening, so couldn’t get access to the code

Thanks Martin
No code and about 40 sheets missing

Tried it didn’t work, same result.
I have attached the file to see if you can get into it42785-StaffTrainingRecords

Andrew
Thanks very much for this
I have had to massage my files a bit but what you say makes sense and works a treat.

I will deploy tomorrow and hopefully!! no problems

The code hasn’t changed since you developed it but I would be interested in seeing how you would change

Om another matter can you recommend a good text(s) as a reference for VBA coding

Andrew hi
I have finally managed to get this sorted. Seems AD doesn’t differentiate between case although it does seem to in some cases!!

Next question, how can I make the userform dynamic because the signatures used by the various staff range from 1 – 8 and in varying order. This makes the userform look pretty awful.

Thanks Andrew
The login details I have, have come from our IT guys. The IT guys inform me that there should not be any difference in the login verification for online or VPN even though there clearly is some difference. We do not have different passwords for each.

Tried your suggestion and confirms my login

Have searched around and tried various forms of the same, VBA.Environ(……, Environ$(……, VBA.Environ$(…., checked the ToolsReferences and all seems OK. I have even tried concatenating the Domain name as part of the UserName

Andrew
Another twist. I was certain the code worked fine after I changed the username detail. But connected via wifi at work didn’t want to recognise my login. However, just checked gin at home via VPN and all works fine.

Not sure what that is about!!!

Andrew and RG
Discovered that we use the user initials as the Environ”UserName” so as soon as I changed mine to PGC every thing worked for both examples.

Andrew I tend to agree with you the code is much cleaner, need to examine it more closely to understand how it works!

Thanks Andrew
I can’t see any difference in the way this performs. It still loads the blank form in the background and then pops the warning message. So my UserName is not being recognised.

Sure
It is in the attached *.dotm, frmSigInsert

Yes
Checked my username and it is of the same format “Phil Carter”. As stated I have set myself as supervisor, not that I need to use the signatures, but because I need to verified that the links work.