Posts by ailc

Re: Clear Column A On One Sheet

Thank you

Hi

Can anyone help me how do I specify in the code to clear Just column A?

Re: Drop-down List Of Job Names Displayed Within One Sheet

Ok one question though if a range in set up for all sheets starting with A and then say new sheets are added to the workbook that begin with A so now appear in the list, will i have to go back each time a sheet is added and edit the name range to ensure it also includes the new sheet that starts with A in the A range?

Re: Drop-down List Of Job Names Displayed Within One Sheet

Ok forgive me I am unfamiliar with this process.

The sheet 1 say is the sheet which holds the list of names
so when I go to the other sheets do I still go to Validation - Data - List and enter say

=OFFSET(INDIRECT(ADDRESS(MATCH("A*/ P*/ C*",Sheet1!$L$2:$L$1001,0)+1,2)),0,0,COUNTIF(Sheet1!$L$2:$L$1001,"A*/ P*/ C*","),1)

as the source code. I receive an error

Hi,

Any help on the below query would be very much appreciated.

I have a list of jobs being displayed using the following code. All sheet names that start with AJ, CJ and PJ within the workbook are how the list is created.

Sub ListSheets()
    Dim sht As Worksheet
    Dim lRow As Long
    Dim rCell As Range
     
     
    With Sheet1
      
        Set rCell = .Cells(2, 12)
    End With
     
    For Each sht In ActiveWorkbook.Worksheets
        Select Case UCase(Left(sht.Name, 2))
        Case Is = "AJ", "CJ", "PJ"
            lRow = lRow + 1
            rCell(lRow, 1) = sht.Name
             
        Case Else
        End Select
    Next sht
     
     
End Sub

what i want to do is create a drop list within each job sheet within the workbook that will display the names of the jobs above. Now the thing is I cannot choose the range like normal from data - validation - list as I will not know how many job names will be displayed so I dont know how many cells to include in the range.
Can anyone advise how I could go about this?

Re: Drop-down Menu With All Sheet Names

Hi Guys,

Thanks for your help I have it workin now. There is one sheet name though that doesnt appear just a blank cell so I am going to look into why this maybe?

Another query I have is how to do I use dat validation list when the cell range might be different depending on the amount of sheet names?

Re: Drop-down Menu With All Sheet Names

I'm afraid not I have added this code to a module

Sub ListSheets() 
    Dim sht As Worksheet 
    Dim lRow As Long 
    Dim rCell As Range 
     
     
     
    With Sheet1 
        .Columns(1).ClearContents 
        Set rCell = .Cells(2, 1) 
    End With 
     
    For Each sht In ActiveWorkbook.Worksheets 
        Select Case UCase(Left(sht.Name, 2)) 
        Case Is = "AJ", "CJ", "PJ" 
            lRow = lRow + 1 
            rCell(lRow, 1) = sht.Name 
             
        Case Else 
        End Select 
    Next sht 
     
     
End Sub

I also added the following code to sheet1

Private Sub Worksheet_Activate() 
    Call ListSheets 
End Sub

Private Sub Workbook_Open() 
    Call ListSheets 
End Sub

I then save the workbook close it and reopened it and entered sheet1 and there is no list of names appearing even though there is about 70 sheets that start with either AJ, CJ and PJ.

Re: Drop-down Menu With All Sheet Names

Now when I add the code that Dave provides if I go to activate the sheet or open the workbook I now receive no error but also nothing is displayed either.

I am very confused on how to get this to work?

Re: Drop-down Menu With All Sheet Names

I looked up the help for this particular error and one of the things it did say was :

Quote

The method can't be used in the applied context. Specifically, some Range object methods require that the range contain data. If the range does not contain data, the method fails.

Now this sheet is empty that the list will appear in, is this the issue?

Re: Drop-down Menu With All Sheet Names

Ok once I add the code advised by you to a standard module and then the calling of the ListSheet in the sheet when workbook is open and worksheet is activated I receive the following error:

Quote

Run-time error '1004'
Select method of Range class field

Re: Drop-down Menu With All Sheet Names

Ok sorry for the constant questions but I have added this code to a new sheet and no info is appearing in this sheet i.e. no sheet names that start with Aj, Cj, PJ?

Any reason why this may be?

Re: Drop-down Menu With All Sheet Names

Ok yes I think I understand, so could I use the code mentioned to maintain the job list in one sheet and then use Data - Validation within each sheet I require the list to appear?

Sub Workbook_Activate()
    Dim sht As Worksheet
    With Me.JobList
        .Clear
        For Each sht In ActiveWorkbook.Sheets
            Select Case UCase(Left(sht.Name, 2))
            Case Is = "AJ", "CJ", "PJ": .AddItem "" & sht.Name
            Case Else
            End Select
        Next sht
    End With
     
End Sub

and then use Data - Validation list within each sheet referencing this list, correct?

when I first of all add the combo box to one sheet and name it JobList and add the code above to the sheet, I receive the following error:

Quote

Compiler Error:

Method or Data member not found

Can anyone help me out here. Thanks

Re: Drop-down Menu With All Sheet Names

Hi,

If anyone can provide some feedback or advise with this request I would be very grateful.

What I want to do is have a drop down list in each sheet which contains all sheet names that start with A, C or P. This will provide all job sheets with the option of choosing a job from the list that it depends on. so this can be done as follows:

Dim sht As Worksheet 
With Me.ComboBox1 
    .Clear 
    For Each sht In ActiveWorkbook.Sheets 
        Select Case UCase(Left(sht.Name, 2)) 
        Case Is = "AJ", "CJ", "PJ": .AddItem "" & sht.Name 
        Case Else 
        End Select 
    Next sht 
End With

But the questions I have is where will I put this code so the same list will appear on all sheets that begin with A, C and P and also how do I get the list to save the option choosen from the list on each sheet.

I really would appreciate any help.

Hi,

If anyone can provide some feedback or advise with this request I would be very grateful.

What I want to do is have a drop down list in each sheet which contains all sheet names that start with A, C or P. This will provide all job sheets with the option of choosing a job from the list that it depends on. so this can be done as follows:

Dim sht As Worksheet
    With Me.ComboBox1
        .Clear
        For Each sht In ActiveWorkbook.Sheets
            Select Case UCase(Left(sht.Name, 2))
            Case Is = "AJ", "CJ", "PJ": .AddItem "" & sht.Name
            Case Else
            End Select
        Next sht
    End With

But the questions I have is where will I put this code so the same list will appear on all sheets that begin with A, C and P and also how do I get the list to save the option choosen from the list on each sheet.

I really would appreciate any help.

Re: Drop-down Menu With All Sheet Names

Well because the list contains the names of all the sheets which are jobs and they are constantly updated and edited so storing these values in cells would also mean having to update them on a regular basis to ensure the list that is created from them is also correct.

Re: Sum A Range Of Cells In Another Workbook

Thank You

Hi,

Can anyone tell me how I set a cell in a workbook say called Holidays so that it is equal to the sum of a range fo cells in another workbook.

Say cells range (D39:L41) in workbook called Test in Sheet Services

Re: Drop-down Menu With All Sheet Names

The combo boxes on all sheets will contain the exact same info but when someone chooses a value i want to save it and for that value to stay chosen in combo box when you go back into that sheet again

Re: Drop-down Menu With All Sheet Names

Also can I ask you something, if i decide to add a combobox to 10 different sheet, the combo boxes have to have 10 different names and how do I get excel to save the options chosen?

Sorry about all the questions but just a bit unfamiliar with this process at mo?

Re: Drop-down Menu With All Sheet Names

Ok so now it is working,

when I first open this workbook and the combop box is empty within that cell then when I click on another sheet and go back into this particular sheet with combo box all names appear in menu.

Is this the correct functionality?

Thanks agaain for all your help