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

Regards

KP

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

thanks

KP

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

Update: I fixed the directory thing using 2 string variables but how to do exit sub from a function when condition is not met? i may not be coding the efficient method here but everyday is a learning.. Thanks in advance for the help

Please find the zip file attached, it has all the files and utility. While drafting this file i was able to build a check inside the code for file open check, it worked first time but second time Error 53 came on it.. function to look for is checkfile open and utility.xlsm has code

Kindly share your insight

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

Regards

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

Thank you Sir Roy and Sir Carim for your inputs

Carim I inherited the code from existing macro, i am sorry not sure of the source.

royUK i ll try to implement your code and let you know, thanks much

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

Regards

Pranav

no it is not and i am not able to get my personal computer online, i ll post the sheet soon

thanks

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"
Else
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

Thank you. I realized the issue, i was using sheet on sharepoint and it had links to other sheets, that is why when i tried Vlookups it was not working and upon seeing ur solution working and trying in dummy sheet, i realized it.. Thanks much Bosco_yip

Respected forum members

Seeking your help for formula. I have two sheet, i want to match values of Col A in Sheet1 with Col A of Sheet2 and return the value of Col B adjacent column of Sheet 1 in Column E of Sheet 2 when there is match on Data of Col A's across sheets. Kindly help

Thank youMatch and return.xlsx

Thanks Fluff, Justin - i tried the methods suggested, i was able to utilize those with my objects - it worked like charm

Thanks Jolivanes

Thanks for your response, i ll try the solutions today

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