Posts by ape

    Hi Roy thanks for responding. Below is the workbook code, not sure if it's of any help. There are lots of other sheets but they contain data only. I have also listed the actual code in the list box. I can update the RowSource from any where else whit in the main from e.g. Combo boxes but not from within the list box itself. The list box simply contains list of work sheet names. This RowSource up date should simply display the data in the selected work sheet.

    Hi Roy

    I have several work sheets each with a name and a unique reference number that are listed in a list box (hence looping). The code should display the sheet contents after the list box RowSource is updated. This routine works fine from other places within the code but not within the list box itself! I have tried to deselect but it not working. The list box is on a form.

    Thank you

    I have a list box with several lines, when I select a row I would like to change the RowSource depending on the value of ListIndex. However the RowSource does not refresh! Here is the code;

    Public WS As Worksheet

    Public Var1 as Integer



    Thanks Roy & Kenneth. The program has many user defined sheets which is set up from selections from user Form ComboBoxes, data will be entered in these sheets and this forms a data base. Once each sheet is defined; a dyamic variable with data range is setup and stored in maned manager. At the start of the the program data base will be listed in the list box. So the user will be able to select an item and the sheet's contents will be shown in the list box. Dynamic range name for each sheet is stored in G1. So changes to code (apart from Case statement, "it was a long day") does not work. Sheet selection using ComboBox works absolutley fine, it is the line selection from the list box which is not working!
    Even code Me.ListData.RowSource = "" is not clearing the box!!

    Thank you for your comments. Indeed value of G1 is the new Rowsource which is loaded everytime a sheet is selected. I had tried address option but rowsource does not refresh. This only occures if line selection is made within the list box, if this is done from another control (as in case one) there are no issues!

    Hi all
    I have a list box which displays various information depending on what the rowsource is set to. Currentley I have a combo box which which is used to select the line number and set the rowsource. However what I would like to do is to set the rowsource when specific lines are selected in the listbox itself. I have tried varioud way but nothing seems to update the list! Any help will be appreciate.

    Here is the code;
    This works :-

    Hi all
    I have an excel VBA application where I disable the X button at application launch, when quitting the application the last sheet remains open and I can't close it! The code I use is;

    Public DisableX As Boolean

    Sub Workbook_BeforeClose(Cancel As Boolean)
    'Close workbook from main form only!

    If DisableX = False Then Cancel = True

    End Sub

    Any thoughts will be appreciated.

    Thanks for the reply. I have a code which generates the a new page page further up in the program. Sheet name is then passed on to be filled with logos headings etc. I tried a similar code but I keep getting the error below which I can't get rid of!

    "runtime erroe 438 Object doesn't support this property or method"
    The picture is in jpeg format


    Private Sub CopyMasterSheet(SheetName As String)
    'Update header, footer and cells from page "Master"
    Dim WsMaster As Worksheet
    Set WsMaster = Sheets("Master")
    Set Ws = Sheets(SheetName)
    With Ws.PageSetup
    .LeftFooter = WsMaster.PageSetup.LeftFooter
    .CenterFooter = WsMaster.PageSetup.CenterFooter
    .RightFooter = WsMaster.PageSetup.RightFooter
    .CenterHeader = WsMaster.PageSetup.CenterHeader
    .LeftHeaderPicture = WsMaster.PageSetup.LeftHeaderPicture
    .RightHeader = WsMaster.PageSetup.RightHeader
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    End With
    End Sub

    Great thanks for the code, more efficient way of setting up. For "YES" or "NO" (hide or show sheet) decision I have setup code below which works well.

    For Each Ws In ThisWorkbook.Worksheets
    For Each CellRange In Ws.Cells.SpecialCells(xlCellTypeAllValidation).Cells
    With CellRange
    If .Validation.Type = xlValidateList Then
    If .Value = .Range("D1:D1").Value Then
    Ws.Visible = True
    Ws.Visible = False
    End If
    End If
    End With
    Next CellRange
    Next Ws

    I have validation data " Yes and NO" set up on a sheet. How can I check if selection is "YES" or "NO"? e.g return TRUE if YES, False if NO
    Any help will be appreciated.
    Thank you