Posts by juergenkemeter

    I have an Excel-Sheet where I want to delete certain rows with a button.
    The condition for deleting a row is placed in column "AF". When there stands a '0' OR a '1', the corresponding row should be deleted.
    Therefore I need some kind of "If" - Code what searches for 1's and 0's in row AF and then deletes the rows where a 1 or 0 is placed....
    hope I made myself clear enough, if not just ask.



    I have the following problem:
    There are several Sheets in my Workbook. One Sheet (overview) has cells with contents like "breast", "colon", "lung", working as a table of contents.
    Furthermore, I have corresponding Sheets named "breast", "colon", "lung", where the details are situated.
    Now, when the user clicks on a cell named "breast" in the overview Sheet, he should be linked to the relating Cell in the "Breast" - Sheet.
    How can the Excel-Hyperlink - Function be improved so that there is also the possibility to set links to certain cells, not only to certain Sheets?


    is it possible to format a whole column as "hyperlink cells"?
    The background is the following:
    I want to input data with an Access Form. Therefore, I want to link my Excel-Sheet with Access. But I have problems with entering new Hyperlinks in the Access Form, because the Excel Cell formatting of the Cell is "Text", and Access can't change the Formatting of a Linked Excel-Sheet...

    Hope you have a tipp for me...

    My situation: I have an Excel Sheet with many Columns and Rows. Big Boss wants a search function for five different columns which accepts substrings and displays all found Rows together for easy printing. Practically a combination of the "find all" function of Excel and an Autofilter... perhaps it ould be possible with an Autofilter alone??

    I already began with working: here you can see a search function which searches a certain row / column and highlights the found cell, one after one (-->loop).
    The function marks a certain Column / Row as "search room", here:
    "Rows("2:2").Select" . This could be changed for searching five different columns, as described above.
    Sub TargetSearch()

    Static varSuchmich As Variant
    Dim varTransfer As Variant

    On Error Resume Next

    varSuchmich = InputBox("Please enter Target to search for: " _
    & vbCrLf & "click Cancel to Exit; click OK to find next", _
    "Submit Query:", varSuchmich)

    If varSuchmich <> "" Then

    varTransfer = varSuchmich

    cells.Find(What:=varSuchmich, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate

    ActiveCell.Interior.ColorIndex = 6

    If Err Then
    MsgBox "The Target ''" & varSuchmich & "'' could not be found .", , "Search result:"
    End If

    Selection.Interior.ColorIndex = xlNone

    End If

    If varTransfer <> "" Then
    varSuchmich = varTransfer
    End If

    End Sub
    Perhaps I can put a Button on the Sheet which calls a Userform. On this Form, there are five Textfields for each column where the user enters the Substring to search for.
    Each Textfield would then be related to five different Search functions (because you have five different columns to search through; the columns can be changed in my search function, as told before.

    But the problem is that I don't know how to display all found rows, and nothing more, so the user can only see the found target rows.

    I tried to explain it best, if there are problems in understanding my issue, please ask! I would be grateful if someone has a hint towards the solution.


    I have an Excel database with a userform and three sheets. In the Sheets, there are columns with titles (="Targets").
    In the Userform there is an overview in form of a list lield, where the user can get an impression of which targets are currently in my database.
    Here is the code of the userform, where the list field entries are made:
    Private Sub UserForm_Initialize()

    Call fncHasUserformCaption(False) ' UserForm ohne Titelleiste laden, damit es nicht verschoben werden kann
    DateAndTime.Caption = Format(Now, " hh:mm") 'Anzeige von Datum und Zeit
    ActiveWorkbook.Unprotect ' keine Sperrung des Workbooks
    Application.DisplayFullScreen = False 'kein FullScreen - Modus

    ' Hier die aktuellen Targets eintragen!
    TargetUebersicht.List = Array("AKT1_PKB_alpha", "AKT2_PKB_beta", "AKT3_PKB_gamma", _
    "AKT_PKB", "APC", "Aurora", "CCKI_PDGF-R", "CD13", "CD19MAb", "CD24", "CD46", _
    "CD55", "CD59", "CEA", "CholinKinase", "CISK_SGK3", "CK2", "c-met", "DNMT", _
    "EAG_K+channel", "EPCAM", "FactorH", "FAK", "FLT3", "HDAC", "Her1", "Her2", _
    "Her3", "Her4", "HIF", "HSP-70", "Igfr", "L1-CAM", "Matriptase", "mTOR", _
    "PAI-1", "PAI-2", "PDE_inhib", "PDGF-R_MAb", "PEDF", "Pin1", "Provasopressin_MAb", _
    "PSCA_MAb", "PSMA_MAb", "PTEN", "src", "TIE2", "TRAIL-R2_MAb", "TROP2", "uPA", _

    End Sub
    Now my question: is it possible to link each entry of the list field with the corresponding target in the excel-sheet??

    All in all, I would need three list fields on the user form which would link to a certain sheet: Xenografts, Cell lines and Clinical samples.

    Thanx for hints,

    Here is my Code,hope that helps:

    varSuchmich = InputBox("Please enter Target to search for: " _
    & vbCrLf & "click Cancel to Exit; click OK to find next", _
    "Submit Query:", varSuchmich)


    I have tried your code, but there is a Message " User-defined type not defined", referring to
    "New PowerPoint.Application"

    I also tried the following code. Here, Power Point is loaded, the message "starting slide show" is on screen, but then Power Point closes completely without any message!
    Here the code;
    Option Explicit
    Sub PPStarten()
    ' Verweis auf Powerpoint Library setzen
    Dim ppApp As Object
    Dim ppPre As Object
    Dim sFile As String
    sFile = "V:\_Projects\Target_validation_DB\_Overview\Demo\DEMO_DB.pps"
    Set ppApp = CreateObject("PowerPoint.Application")
    ppApp.Visible = msoTrue
    Set ppPre = ppApp.Presentations.Open(sFile)
    Set ppPre = Nothing
    Set ppApp = Nothing
    End Sub