Posts by GOLDENEXCEL

Re: Check if sheet hide then unhide them

Hi,

This code could help you:

Sheet15.Visible = xlSheetVisible = Sheet15.Visible = False

Cheer!!!

Re: how to lock excel sheet with password 2007

Hi masoomali,

you can try this code to protect work sheet 2007:

Global Const pw = "abc2007"


Sub Pro_Sh()


Sheet1.Protect pw


End Sub

Re: Calculate interval between phone calls for multiple agents in one column

Hi Jaffey,

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:

=IF(D2="","",D2&TEXT(B2,"dd-MM-yyyy")&"."&TEXT(COUNTIFS($B$1:B2,B2,$D$1:D2,D2),"00#"))

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))))

Cheer!!!

Re: Could not get list property. invalid argument

HI,

Your code take error because you use:

With.....

End with

Re: Hide and unhide with checkboxes - multiple checkboxs do multiple different things

Hi,

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:

Private Sub CheckBox1_Click()If CheckBox1.Value = True ThenRange("6:13").EntireRow.Hidden = TrueElseRange("6:13").EntireRow.Hidden = FalseEnd IfEnd Sub

Cheer,

Re: VBA moving excel object to cell containing current date

Hi dstetar,

This code may help you.

Private Sub Workbook_Open()

Dim i As Integer
Dim dt As Date

dt = Format(Now(), "dd-mm-yyyy")

Sheet1.Activate

For i = 1 To 500

If Sheet1.Cells(1, i).Value = dt Then
Sheet1.Cells(1, i).Select
End If

Next i

End Sub

Cheer!!

Re: Vba code to know how many excel file are opening and the name of it

Hi AlanSidman,

That fantastic solution, thank for your helping.

Have a nice day

Dear All,

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,

David,

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.

David,

Re: Macro that changes file path every month

Hi,

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

Sub Change_Formulas()

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

End Sub

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.

Thanks,

Re: Conditional Formatting with Multiple Criteria Formula

Hi onexc,

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.

Thanks.

Re: Sum elapsed time between events, excluding weekends, for each customer in column

Hi Jeffy,

Try this:

Past this formula into Cell G2 and drag down in order to let Excel refill the formulas automatically

=> =IF(A2=A3,"",IF(TEXT(D2,"ddd")="Sat",(NETWORKDAYS(VLOOKUP(A2,$A:$D,4,FALSE),D2)-1-MOD(VLOOKUP(A2,$A:$D,4,FALSE),1)+MOD(D1,1)+E2),NETWORKDAYS(VLOOKUP(A2,$A:$D,4,FALSE),D2)-1-MOD(VLOOKUP(A2,$A:$D,4,FALSE),1)+MOD(D2,1)))

Hope it will bring the result you want

Thank

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

Hi cshortt,
For the VBA to go to the next worksheet to get the next set of data and paste it, you can try this:

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

Thank

Re: ComboBox Table Column

Hi,

Please send me an attached file of above example.

Re: Compare value between two Textbox

Thanks shknbk2 for your opinion.

Good day!

David.

Dear All,

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:

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:

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:

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:

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:

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.

David,

Re: Code to Automatic Enable Macro when Workbook Open

Hello all,

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!

David,

Dear All,

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.

David,