List box not working

[ysherriff]

Hi,

Need help with listbox populating. When I click on the up and down button, it gives me an error message. Please see below.

The listbox that is causing the error is on the worksheet titled "hospital revenue view"

The vba code are on the same sheet in the vba module and it is password protected "ops"

It is a lengthy code and if you have a code that does it much simpler, I would appreciate it.

 

[ysherriff]

here is the error that shows up when I debug.

 

[Hui]

Why not link the two arrows directly to the cmdDownLevel_Click() and cmdUpLevel_Click() subroutines ?

I'd also remove the Public statements before them

 

[ysherriff]

Thanks Hui. The issue is that the list box data are unique. As you can see from the below code, one code is based on sheet12 and the other is based on sheet 16.

So I can not link them because the populated data is different. For some reason, even when I remove the public statement, I still get the run time error code and I can't seem to know how to fix it. That is an area of weakness of mine, is how to debug codes appropriately.

If there is an easier way to do populate a list form based on dependent list, I would really like to know how.

Thank you.

 

[Hui]

Can you explain what your actually trying to achieve with the list box and arrows?

 

[ysherriff]

Will do sir. There is a sheet titled "Hospital Control" which has the dependent list that should populate the list box based on user selection that is on the sheet titled "Hospital Revenue View". The buttons the user will select will either drill up or down to get the required data.

This is similar to the list box operation on the sheet titled "Trend View" which is working properly. The only thing I did different was just add different data that is populated in the list box. The functionality is the same.

Hope this helps. The original code was developed by someone else. I am just trying to mimick and learn more about list box rather than using combo drop box for dependent lists. I think list boxes are more efficient and better use of space in dashboard reports

 

[p45cal]

try:
Call Sheet16.cmdUpLevel_Click
instead of
Application.Run "sheet16.cmdUpLevel_Click"

 

[ysherriff]

I updated it to remove the application.run and still have one issue.

When I click on the down arrow button, I still get this error message on the code below highlighted in yellow. The list populates correctly but wondering why the error message is generated. This doesn't happen with the up arrow. Password to open vba module is "ops".

Thank you guys for your help.

 

[ysherriff]

I put an error handling code to skip the error. Everything is working but for some reason, the error code still shows up. So I am skipping over the error. If someone can really help to see why, I would be forever grateful.

Still learning advance VBA programming.

 

[p45cal]

You have a line:

ThisWorkbook.Sheets("Hospital Control").[SELECTED_LEVEL_TREND_HOSP] = "HOSPITAL"

in Sub cmdUpLevel_Click() in sheet16's code-module.
I can't find SELECTED_LEVEL_TREND_HOSP anywhere… nor can the code.
What named range should this be?

 

[ysherriff]

YES..... IT WORKED.. THANK YOU. WHAT AN IDIOT I AM. THE NAME RANGE WAS DIFFERENT........

THANKS MUCH