• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help - Input Userform

eyepods

eyepods

New Member
Hi All, I am an intermediate excel user and have used this forum to create a number of simple dashboards and reports but, I would like to understand how to create input userforms which will allow to 'add', 'delete' and 'update' values. I am no VBA guru and am struggling to create a working userform. I have attached a sample dashboard I am working on to indicate elderly care Dependencies which works in test but I need to included a basic input route to allow users to add amend and delete records in worksheet. Can anyone help or offer advise. please see attached worksheet, (some tabs are hidden) all data is anonymous.

Any help appreciated.

Thanks
Mike
 

Attachments

  • Dependancy-Dashboard.xls
    254.5 KB · Views: 24
eyepods said:
Hi All, I am an intermediate excel user and have used this forum to create a number of simple dashboards and reports but, I would like to understand how to create input userforms which will allow to 'add', 'delete' and 'update' values. I am no VBA guru and am struggling to create a working userform. I have attached a sample dashboard I am working on to indicate elderly care Dependencies which works in test but I need to included a basic input route to allow users to add amend and delete records in worksheet. Can anyone help or offer advise. please see attached worksheet, (some tabs are hidden) all data is anonymous.

Any help appreciated.

Thanks
Mike
Click to expand...
Hello Eyepods,
Do you want to add, delete & update them in 'data' sheet?
Regards,
Thangavel D
 
eyepods said:
Hi, Yes, I hope to use a user-form to Add, Delete or Update the values in the Data Tab.

Amy help appreciated.
Click to expand...
Hello, Could you please clarify below queries.

If you want to
Add - Column C = auto number, rest 47 columns to be entered? what are the mandatory fields?
Delete - Whole row to be deleted?
Update - change some values in the existing data?
Regards,
Thangavel D
 
Relax,
Here it is evening now, If nowbody is faster ,I'll make a good working example tomorrow.
 
Hi Thangavel / Belleke
I have set Column C to = auto number, thanks, I didn't know of that option. :) The only Mandatory fields are Column C, Room Number (Number Value) & Column D, Name (Text Value). The record must have a room number and a name allocated to room. All other values are optional.
I would like to set the input route too:

Add - add a new Room number and Name (Mandatory)
Delete - Whole row to be deleted?
Update - change values in the existing data? (In any Cell, including Mandatory Cells)

No rush, enjoy your evening and have a great weekend.

Mike
 
Hi Belleke. I assume you mean Program Language :) As I am no programmer I will leave choice to you, VB, PHP, what ever works..

Regards
Mike
 
Hi Belleke, Thanks for userform. The userform look good and I like the search option on the right, i didn't think of including this option.

I see you created a new 'data' tab for the userform, how can I change the titles from the form (remove text 'KPI') and formatting (i.e. fonts size and colour). How can I update the formula linked to the 'Resident Dependency Report'.

Thanks again for your help, appreciated.
Mike
 
Hi Belleke.
Can you please let me know how to apply the userform to the dashboard report, I have tried but password/restrictions have been applied of the sheet. The report section is not working correctly and flagging errors. can you help...

Thanks
Mike


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !
​
 
eyepods said:
Hi Belleke, Thanks for userform. The userform look good and I like the search option on the right, i didn't think of including this option.

I see you created a new 'data' tab for the userform, how can I change the titles from the form (remove text 'KPI') and formatting (i.e. fonts size and colour). How can I update the formula linked to the 'Resident Dependency Report'.

Thanks again for your help, appreciated.
Mike
Click to expand...

The report section is not working correctly and flagging errors. can you help...
 
eyepods said:
The report section is not working correctly and flagging errors. can you help...
Click to expand...
Hi,
R you getting #N/A error. If yes this is due to the deletion/removal of Product. You can use Iferror formula in report section to overcome this error. As example below:
=IFERROR(VLOOKUP(E24,Data!B3:R101,2,0),"Left") : you will see Left in those cell or
=IFERROR(VLOOKUP(E26,Ratio!D5:E11,2,FALSE),"") : you don't see anything

Please find the updated sheet with Iferror formulla.

Regards,
Thangavel D
 

Attachments

  • Dependancy-Dashboard.xls
    313 KB · Views: 7
Last edited:
eyepods said:
Hi Belleke, Thanks for userform. The userform look good and I like the search option on the right, i didn't think of including this option.

I see you created a new 'data' tab for the userform, how can I change the titles from the form (remove text 'KPI') and formatting (i.e. fonts size and colour). How can I update the formula linked to the 'Resident Dependency Report'.

Thanks again for your help, appreciated.
Mike
Click to expand...
Can you please tell fonts size & colour? i'll help you to update them in userform.
Regards,
Thangavel D
 
Hi Thangravel, thanks for getting back to me, i have been pulling my hair off trying to fix the error in the 'Resident Dependency Report'. The problem appears to happen when you use the scrollbar. When you scroll through the names the Report below (Resident Dependency Report) should update the Name (Product) value, this then displays the correct values (DVSelectBox) and chart info but it does not work error (#NAME?).

Any help appreciated?
Mike
 
Hello Mike,
I got it. There is a change / wrongly named range mentioned in the scrollbar list.
That I've corrected & pls find the updated sheet as well.

Code: 
Sub ScrollBarListe_Change()
    NewName = Application.Evaluate("=INDEX(Rnames , " & [OffsetValue] & ")")
    [DVSelectionBox] = NewName
End Sub

Regards,
Thangavel D
 

Attachments

  • Dependancy-Dashboard.xls
    315.5 KB · Views: 20
Thangavel said:
Hello Mike,
I got it. There is a change / wrongly named range mentioned in the scrollbar list.
That I've corrected & pls find the updated sheet as well.

Code: 
Sub ScrollBarListe_Change()
    NewName = Application.Evaluate("=INDEX(Rnames , " & [OffsetValue] & ")")
    [DVSelectionBox] = NewName
End Sub

Regards,
Thangavel D
Click to expand...

Excellent.!! Thanks Thangavel. Scrollbar link to dataset working, I wouldn't have spotted the typo/error in the named range. Much Appreciated..

Mike
 
eyepods said:
Excellent.!! Thanks Thangavel. Scrollbar link to dataset working, I wouldn't have spotted the typo/error in the named range. Much Appreciated..

Mike
Click to expand...
You are welcome.
When Belleke try to crate userform, she used her own name ranges. Which is different from yours. This leads to this error.

Any other changes to be done on this userform?

Regards,
Thangavel D
 
You must log in or register to reply here.
Back
Top