Posts by KayPee22

    Hello Respected members,

    I am trying comboboxes for first time and i am not able to figure out how to tell button to perform action based on values selected in combobox.

    I understand how to trigger a macro from button but i do not get how to trigger specific action when the button has/should follow critieria from drop down values of combobox.

    I am attaching the workbook where my ask is to send email to students with specific subject and priority - please look at sheet named as requirement in attached workbook

    Thanks in advance

    Thank you royUK, with Now()-A2 there was issue when number of hours are greater than 24, so i used =INT((B2-A2)*24) and then appended "hrs" by concatenation, Int formula is better when the tracker goes beyond 24hrs.

    Again thanks much for the help



    Hi royUK

    Thanks for your response

    Yes i read that part, its easy when there are two columns with value in it and its calculated in 3rd, i was trying to explore the options where the subtraction happens based on current system time. There's no A2-B2 *24, but i am looking for guidance on (Current Systemtime - Time in Col A2).

    it would be great if you can point me in that direction, sorry if i am still missing something



    Respected community members

    Requesting help

    I have date field cell of Type Date and value "5/5/2021 12:48:40 PM", please let me know how can i calculate the duration (how much time has passed since in hours:minutes) based on current system time? i have read about duration and formulas but not able to make duration in hours or minutes

    I think i did it, so i used End after the if, i am so stupid.. will keep improvising and post update by End of day, Thanks forum members.. would still look up to you Roy for asking for the file attachment and i found solution while writing the code "i wont be too excited as of now" but ll go till end before i post update

    I read back what i asked for and i will try my best to rephrase. Seeking back help

    1. File is open or not is working fine (that s inherited code no issue there)

    2. Checkfileopn function takes the values of sheet inside the workbook, which is already present Checkfileopn(workbookpath &SheetA)

    3. I have 5 sheet names fixed and all are passed into objects SheetA , B,C,D ,E respectively eg:SheetA=myclass.xlsx, sheetB=myrooms.xlsx etc

    4. My ask here is that this function Checkfileopn(workbookpath & giveName) where giveName is a string variable should get value of SheetA, B, C in iteration so that function Checkfileopn is called one by one these sheets by using for loop or array or anything

    5. How to put these specific Sheetobject names into one variable giveName and loop it with sheetnames so that call Checkfileopn() is called one by one by these, then i think it will fit into True False thing i already have as i listed above


    IsFIleOpen = Checkfileopen(ThisWorkbook.Path & "\" & giveName)

    Respected forum members seeking input on checking if file is open when multiple files are there to check

    as of now i am using a boolean variable and checking file one by one by repeating this 5 times for 5 files, please can you suggest how can i make it run 5 time in loop?

    Thanks in advance for your suggestions

    IsFIleOpen = Checkfileopen(Workbookpath)
    If (IsFileOpen = True) Then 
    MsgBox "This file is open" & Filename & "close it"
    Exit Sub
    End IF

    Sorry for late response, i created a temp column with values then used the find formula there to make it work. As the original column had formula instead of values. When its values in column then find works like charm.

    Thank you for your inputs My Roy and Mr Carim



    Requesting help

    Please can someone suggest how to put an If Else condition on cell which is populating values based formula. I cant get rid of formula

    if it was simple text following code should have worked

    If Not Sheet.columns(AB).Find(what:= "Nine") is Nothing Then
    Msgbox "Found"
    Msgbox "not found"
    End If

    but here my problem is that column AB is not text, value "Nine" is displayed as result of Vlookup formula and i have to keep the formula there in this column.

    Kindly share your valuable inputs

    Thanks Justin, i will give a try. I want to do find and replace for entire column but instead of hard coding the column as A or B or V, i want to use the column name such as RollNo so that when RollNo moves from Col B to A or to C, it still does Find Replace

    respected forum members, please can you suggest how can i do find replace using the column header, instead of column alphabet

    Columns("V").Select        ''''instead of Columns("V").Select, i want to select Columns("Name_Of_Header").Select
     Cells.Replace What:="Ms ", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=Fals

    Instead of Columns("V") selection, i want to do Columns("Name_Of_Header").select

    Kindly suggest

    Respected forum members

    i was looking at trim and replace as worksheet vs vba method but i could not find any material which could help me understand the difference b/w vba.trim vs trim or vba.replace vs replace and how is one better than other. it would be great if someone can point me to the study link where i can find differences. Or can describe/teach in this thread would also be great.

    Thanks in advance for your help