Notes Userforms

[dannyok90]

hi all,

I'm thinking of creating a user form to help me track notes and checking.

I'm not a novice with VBA and user forms butequally im not a master and maybe not quite an intermediary user either haha.

Basically i have spreadsheet sheet for estimating jobs. It has rows representing subtasks with a description, cost and some calculations etc.
at the end of each row id like a link that says 'notes' that opens the user form with two text entry fields side by side,
one for notes and the otherfor the comments of the person checking the notes and maybe even a selectbutton to say 'approved'

The issue being, im running some vba to addnew rows to the estimate, how would the link to the notes userform at the endof each row know the notes
are for that particular row only and openblank?

hope I haven’t over complicated myexplanation!

Any help appreciated

Thanks
Dan

 

[Tim_Excel_]

Hi Dan,

Not much knowledge on opening UserForms with hyperlinks, but did some searching around and I think the FollowHyperlink event may suit your needs:

https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.followhyperlink

If I am not wrong, you can get the range from the clicked hyperlink thereby being able to specify the specific row the UserForm should refer to.

 

[MickG]

Try this:-
https://app.box.com/s/y0anuuffs2sawc4kvmzqu2gplvsh63te

 

[dannyok90]

mickG

That is exactly what I envisaged! thankyou so much!

Dan

 

[MickG]

You're welcome

 

[dannyok90]

MickG

How could I get this to work across multiple sheets that are the same?

 

[dannyok90]

You're welcome

Following on from my last, here's a link to it in practice. It works great!

how could I get it to work across the other sheets without it over writing the contents on the other sheets?
also, I've tried to incorpotate a double click command so I can only click on that cell, rather than any cell in the column to open the userform? but not so sure
I often bastardise bits of vba I find on the internet to my own needs so im not all that good writing stuff from scratch (yet) getting there though!

https://drive.google.com/file/d/1vPa0x_I7Fh2Rggnfk00qtOwL7lcIkaLx/view?usp=sharing

 

[MickG]

Try your modified File !!

I have altered the first 2 sheets to double click, you will need to update the rest as shown in Worksheet Module.

The comments and notes are now held on sheet "Log"

Each sheet rows are held in the columns of sheet "Log" and each sheet in a different Row based on the sheet "CodeName" (sheet1,sheet2 etc)

NB:- when I first Opened the Workbook "Userform1.show" would not work, I eventually got it working, but if you look at the code in any worksheet module, you will see the word "Userform1" starts with a lower case letter, which says to me there is something wrong, but it was working when it left me !!!

https://app.box.com/s/6t3m7bz59s28b2qrdeubbow1vo5tx2wn

 

[dannyok90]

Try your modified File !!

I have altered the first 2 sheets to double click, you will need to update the rest as shown in Worksheet Module.

The comments and notes are now held on sheet "Log"

Each sheet rows are held in the columns of sheet "Log" and each sheet in a different Row based on the sheet "CodeName" (sheet1,sheet2 etc)

NB:- when I first Opened the Workbook "Userform1.show" would not work, I eventually got it working, but if you look at the code in any worksheet module, you will see the word "Userform1" starts with a lower case letter, which says to me there is something wrong, but it was working when it left me !!!

https://app.box.com/s/6t3m7bz59s28b2qrdeubbow1vo5tx2wn

Absolutely fantastic! I cant thank you enough for taking the time out of your day to help me out on this. what a gent. cheers matey!

Dan

 

[MickG]

Your welcome, glad you have it working !!