LIST BOX IN EXCEL

[krishnaa_kumarr88]

Hi ,
Thanks for looking at my query. I am having problem in creating listbox, please help me.
Please check the attachment. According to the attachment,
1. I would like to create a list box in sheet 2 that is named as TPM QRQC in my excel spreadsheet.
2. In my spreadsheet in sheet 2 (TPM QRQC), i have two column , one is for TOTAL NUMBER OF TPM INSPECTION and other for TOTAL NUMBER OF REQUIRES ATTENTION. I linked those two coloumns to sheet1 (TPM FORM) in such a way that if sheet1 turns red then it is counted as 1 in TOTAL NUMBER OF REQUIRES ATTENTION column in sheet 2.

3. What i need is , Depending upon how many TOTAL NUMBER OF REQUIRES ATTENTION (in sheet 2) which add on everyday basics,i would like to create a list box in sheet2 which shows information about the current RED row information in sheet 1 but more like summary way. In the list box it should show only Machine name, Result, Pirana open date which are red in sheet 1.

Please help
Thanks

[Rudi]

Hi,

I could suggest having a Pivot Table that simulates a list box.
It will be 100 times easier to manage and it is dynamic...it will update automatically based on the conditions in sheet 1.

See sample attached...

TPM Timing home2bb.xlsm

[krishnaa_kumarr88]

thats brilliant. Thanks a lot. Its working great. Thanks :)

[krishnaa_kumarr88]

Thanks for your brilliant work. The problem in the above spreadsheet is ,only first 8 pivot table list is added automatically in sheet 2 based on the conditions in sheet 1. Anything added more than 8 or 9 in sheet 1 is not added automatically in sheet 2. Do you know the reason?
thanks

[HansV]

I see that you removed the attachment from your first post in this thread.
Please don't do that - it makes a thread hard to understand for others who read it.

[krishnaa_kumarr88]

Hi Rudi, I sorted out the issue. Its working brilliant now. Thanks for your help

[Rudi]

I'm glad you came right. In the sample file I only set the Pivot data range to row 40 (if I remember correctly).
I assume you modified the range to increase it and include records that fell outside this data area.

[HansV]

I would convert the data range to a table, and make the table the source range of the pivot table. That way, the source range will adjust itself automatically.

You need to stop the clock when you close the workbook. In the ThisWorkbook module:

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopClock
End Sub

(I'd remove the clock altogether - since the code to update it runs every second, editing the workbook is difficult)

[krishnaa_kumarr88]

hI RUDI AND HANS.
Thanks a lot for your reply. Sorry to disturb you again. I got same problem again. I am really poor in pivot table :(.

1. Please see the attachment, I just added one more column called Module in A 23. Becaue of that i am not getting proper pivot table source range in sheet 2. I would like to set the pivot data range to row 600. Can you please help me in that?
2. I would like to get your suggestion in my following idea,
a) Click TPM FORM
b) Select Module to Airbus/GCU/Falcon/AC,etc any one
C) Load rest of the form information
My question is , is it possible to filter the information respected to MODULE Wise? That is , if i select AIRBUS module in the form, i should able to see only AIRBUS module information in the spreadsheet (Which has heading as MODULE in A23) . Simillarly , if i select GCU Module in the TPM FORM i should able to see only information related to GCU Module in the spreadsheet. So i would like to create a filter in the TPM form itself . Is it possible?
Thanks

[krishnaa_kumarr88]

Check the attachment here.

Thanks



hI RUDI AND HANS.
Thanks a lot for your reply. Sorry to disturb you again. I got same problem again. I am really poor in pivot table :(.

1. Please see the attachment, I just added one more column called Module in A 23. Becaue of that i am not getting proper pivot table source range in sheet 2. I would like to set the pivot data range to row 600. Can you please help me in that?
2. I would like to get your suggestion in my following idea,
a) Click TPM FORM
b) Select Module to Airbus/GCU/Falcon/AC,etc any one
C) Load rest of the form information
My question is , is it possible to filter the information respected to MODULE Wise? That is , if i select AIRBUS module in the form, i should able to see only AIRBUS module information in the spreadsheet (Which has heading as MODULE in A23) . Simillarly , if i select GCU Module in the TPM FORM i should able to see only information related to GCU Module in the spreadsheet. So i would like to create a filter in the TPM form itself . Is it possible?
ThanksPost=137272
krishnaa_kumarr88
NewLounger

Posts: 23
Joined: 30 Sep 2014, 15:18
Private messageE-mail krishnaa_kumarr88

[Rudi]

I added the pivot again.
Also modified the form to filter for the module.
I think the code still needs a bit of tweaking to work better, but it is bordering on the limit of my knowledge....

[HansV]

About the pivot table: see the attached version. I incorporated the suggestion I posted in my previous reply.
I don't understand your question about filtering the TPM form, but I see that Rudi posted a suggestion about that.

TPM Timing home LIST BOX modified.xlsm

[krishnaa_kumarr88]

Hi Rudi and Hans
Wow. thats brilliant. thanks a lot for your help :)

[krishnaa_kumarr88]

hI RUDI AND HANS
Regarding List box,
The above attachment is working fine , but i done some modification in my excel and after doing some modification my DATE coloumn in the pivot table is not working fine. Say example, if my spreadsheet show the date as 1/10/12 but in the pivot table under the same cell i am getting as some 1234.3344 . Please see the attachment. Thanks

[Rudi]

You can select the entire H column and format the column to a date format of your choice. The values you currently see in your pivot is the unformatted decimal values behind a typical date format.

[krishnaa_kumarr88]

THANKS

[krishnaa_kumarr88]

Hi rudi,
I am just wondering is it possible to create a chart based on the sheet1 value.
That is total number of rows vs rows in red for the above attachment.

thanks

[Rudi]

Hi,

See the attached with a simple chart example....

TPM filter with Chart.xlsm

[HansV]

Or a slightly different approach: calculate the number of non-red rows:

S22.png

And create a stacked column chart (or stacked bar chart, or even a pie chart) based on the number of rows needing attention and the number of non-red rows:

S23.png

[krishnaa_kumarr88]

THANKS