Posts by GOLDENEXCEL
Re: Calculate interval between phone calls for multiple agents in one column
Solution for you:
Firstly you have to insert one more column at A column and then past below formulas in cell A2 and drag down:
Next you have to past one more formula at cell G2 and drag down in order to get the result you want:
=IF(A2="","",IF(RIGHT(A2,3)="001","First Call for "&TEXT(B2,"dd/MM/yyyy"),C2-(VLOOKUP((D2&TEXT(B2,"dd-MM-yyyy")&"."&TEXT(VALUE(RIGHT(A2,3)-1),"00#")),$A:$C,3,FALSE)+VLOOKUP((D2&TEXT(B2,"dd-MM-yyyy")&"."&TEXT(VALUE(RIGHT(A2,3)-1),"00#")),$A:$F,6,FALSE))))
Re: Could not get list property. invalid argument
Your code take error because you use:
Re: Hide and unhide with checkboxes - multiple checkboxs do multiple different things
The big problem of your issue is you chose wrong check box type (Form Control). thus, you have to select CheckBox of ActiveX Controls. And then Use the following code:Code
Private Sub CheckBox1_Click()If CheckBox1.Value = True ThenRange("6:13").EntireRow.Hidden = TrueElseRange("6:13").EntireRow.Hidden = FalseEnd IfEnd Sub
Re: VBA moving excel object to cell containing current date
This code may help you.
Private Sub Workbook_Open()
Dim i As Integer
Dim dt As Date
dt = Format(Now(), "dd-mm-yyyy")
For i = 1 To 500
If Sheet1.Cells(1, i).Value = dt Then
Re: Vba code to know how many excel file are opening and the name of it
That fantastic solution, thank for your helping.
Have a nice day
I have one issue is that I need a VBA code that can track the number of Excel file are opening and the name of those workbook.
I try to find this type of coding but there is nothing match my purpose.
Thanks for your assist,
Re: How to protect against input in the "Immediate Window"
Hi Kenneth Hobson,
That is a great VBA script. But however I have Two issues related to your coding are:
1- If I try to open one another Workbook and open Visual Basic for Application before I open the workbook that contain your coding, I can reset my coding when Workbook Open by pressing Reset button. eg: When workbook open I need to pop up userform with require passwords in order to access but when i press button Reset, userform will be closed (Your coding can not protect my work).
2- Is there any coding that can run if I press Reset or Break button? eg: If I press the Reset or Break button, all of my worksheets will be hide.
Thanks in advance for your replying.
Re: Macro that changes file path every month
The only one way for you to deal with this issue is you need to use Excel VBA code to change the formulas for you at the month you want.
1Step: you need to use one fixed cell play a role as date, month, and year that you want. eg: You use cell A8 for inputting the date at every month.
2Step: You need to use Excel VBA code to amend all of the formulas in the cell like this:
eg: the formulas you need to change is on Sheet1 and in the cell A1
Sheet1.[A1] = "=HLOOKUP($E910,'\\ABC\Wiggle\Dante\uno\dos\2016\NAV\" & Format(Sheet1.[A8], "dd mmmm") & "\[ABC Schedule" & Format(Sheet1.[A8], "ddmmyy") & ".xlsx]AA'!$B$15:$BI$50,4,FALSE)"
Note: You have to write one by one for the cells you need VBA to change the formula for you. It take long time if there are many cells you want to amed fomula.
Re: Conditional Formatting with Multiple Criteria Formula
If you want the result like this, you should add two more columns for display as serials numbers for both tables and then use each serial as a criterion for the Formula Vloookup.
Finally use Conditional Formatting base on the result of Vlookup.
Note: You must make sure that each serials number are not the same.
Re: Sum elapsed time between events, excluding weekends, for each customer in column
Past this formula into Cell G2 and drag down in order to let Excel refill the formulas automatically
Hope it will bring the result you want
Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee
For the VBA to go to the next worksheet to get the next set of data and paste it, you can try this:Code
Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ''''''Skip sheets which does not relevent If ws.Name <> "Statistics" or ws.Name <> "List - By Vehicle Number" or ws.Name <> "Defaults" or ws.Name <> "TEMPLATE" Then ************your code copy and past here***************** End If Next ws
Re: ComboBox Table Column
Please send me an attached file of above example.
Re: Compare value between two Textbox
Thanks shknbk2 for your opinion.
I have some problem with textbox.
The purpose of mine is that I want to compare value between Textbox1 and Textbox2 by using "IF" condition.
I try many ways but it got error during I attempt.
Below are some codes that I had ever tried:
1- Use with "Val" Function:Code
Dim Price1 as Double Dim Price2 as Double Price1 = Val(Textbox1.Value) Price2 = Val(Textbox2.Value) If Price1 > Price2 Then ------Code Here------------- End If
2- Use with "CInt" Function:Code
Dim Price1 as Integer Dim Price2 as Interger Price1 = CInt(Textbox1.Value) Price2 = CInt(Textbox2.Value) If Price1 > Price2 Then ------Code Here------------- End If
3- Use with "CDec" Function:Code
Dim Price1 as Integer Dim Price2 as Integer Price1 = CDec(Textbox1.Value) Price2 = CDec(Textbox2.Value) If Price1 > Price2 Then ------Code Here------------- End If
4- Use with "CDbl" Function:Code
Dim Price1 as Double Dim Price2 as Double Price1 = CDbl(Textbox1.Value) Price2 = CDbl(Textbox2.Value) If Price1 > Price2 Then ------Code Here------------- End If
5- Use with "Val" Function and Format Text:Code
Dim Price1 as Double Dim Price2 as Double Price1 = Format(Val(Textbox1.Value),"#,##0.00") Price2 = Format(Val(Textbox2.Value),"#,##0.00") If Price1 > Price2 Then ------Code Here------------- End If
Is any one have idea?
Thank in advance for your assist.
Re: Code to Automatic Enable Macro when Workbook Open
Many thanks for all of your advices.
but do you have an other way? because the purpose of mine is that I need to prevent from copy my excel to use with an other computer without authorization from me.
Thus, I need to run macro at all the time that my excel workbook open. Without running macro when workbook open, any person can easily to use my workbook and see my data although I protect passwords both in worksheet and in VBA project.
Thank in advance for sharing your ideas.
Have a lovely day!
I'm David, I have tried to find code for enable macro in excel
but unfortunately, I can not find any code for runinig this cript.
Thus, Is any one have idea?
Thank in advance for sharing your idea.