Posts by parrimin

  • Automatically Add Code To New Workbook

    Hi guys,


    I was trying to add some code through VBA to a new Workbook I'm creating. I'm following the instructions in http://www.cpearson.com/excel/vbe.htm#CreateEvent


    What I need is to add an worksheet_change event procedure, and im trying it as it follows:

    Code
    1. Dim StartLine As Long
    2. With newWB.VBProject.VBComponents(CStr(mysheetName)).CodeModule
    3. StartLine = .CreateEventProc("Change", "Worksheet") + 1
    4. .InsertLines StartLine, _
    5. "Msgbox ""Hello World"",vbOkOnly"
    6. End With


    and

    Code
    1. Dim StartLine As Long
    2. With newWB.VBProject.VBComponents("ThisWorkbook.Worksheets("+CStr(mysheetName)+")").CodeModule
    3. StartLine = .CreateEventProc("Change", "Worksheet") + 1
    4. .InsertLines StartLine, _
    5. "Msgbox ""Hello World"",vbOkOnly"
    6. End With


    but no good results... Any suggestions?

  • Application.filesearch

    Re: Application.filesearch


    I typed what you said in a module, and it remove the ? and puts a "Print ..." There is an error when running. I replaced Print for a MsgBox, and there is an error that says "Incorrect file Name or Number".

  • Application.filesearch

    Hi,


    I have created a macro that must extract data from other workbook. It opens the workbook, with an exist, and isopened control, and then open it. It´s running well, but i found while making another macro, that if the file direction is "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls", then i cant run the exist control. Next you have the code im using. In the first version, i had .LookIn = ThisWorkbook.Path. Now I´ve tried:


    1. Dont put .LookIn, and fName is "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls"
    2. .LookIn = "http://www.xxx.com:8080/"
    .FileName = "Challange%20Cost%20Proposal%20Sheet.xls"


    Both are failing, it says, the file doesnt exist.
    If i try directly WorkBooks.Open "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls", it opens correctly, but i would have a control, because the original file name could change, and the macro would crash if does. Some suggestions?


  • Files With Rare Name Created

    Re: Files With Rare Name Created


    Yes, the "normal" excel files have excel icon.
    I think I solved it. I put those files in a directory where i put read and write permission. I changed permissions, and added modify permissions, and it seems there is no problem, the files are saved whithout creating those rare files, but now, the users can remove the original files, and that is what i dont want...

  • Files With Rare Name Created

    Hi guys,
    I just have shared some files in job, so my boss and their partners can work together, but today I arrived to my desktop, and discovered, there are some new files in the directory. I thought i could remove them, but i discovered if double click on them, they are the same file, but with rare name, like "5A0FCE00", "2036DE00", without extension. I cant understand how windows knows they are Excel Files, if they dont have any extension, and the icon that represents these files, is the icon for unknown type of files.
    The info in that file is very very... important, but its very large file, so i cant see what has changed. i would not remove the last updates if these files are updates from the original... I don´t know what to do. Some suggestions?

  • Sumproduct In Vba

    Re: Sumproduct In Vba


    But, this isn´t running either.

    Code
    1. MsgBox Application.WorksheetFunction.SumProduct((R[19]C[10]:R[199]C[10]=1)*(R[19]C[7]:R[199]C[8]=1))


    Well, if the previous code runs, i dont need this las solution, i can remove cell "A1" when i get the data, but, just out of curiosity, how to make run Application.WorksheetFunction.SumProduct for this purpose??


    Thanks guys for your help

  • Sumproduct In Vba

    Re: Sumproduct In Vba


    Yeahhhh, great h1h, as always happens, the solution was the most obvious, recording!


    The solution is:

    Code
    1. Cells(1, 1).FormulaR1C1 = "=SUMPRODUCT((R[19]C[10]:R[199]C[10]=1)*(R[19]C[7]:R[199]C[8]=1))"

  • Sumproduct In Vba

    Re: Sumproduct In Vba


    Ops, It´s true "k" was missing. Now im trying:

    Code
    1. MsgBox Application.WorksheetFunction.SumProduct("(K20:K200 = 1) * (H20:I200 = 1)")
    2. MsgBox Application.WorksheetFunction.SumProduct((K20:K200 = 1) * (H20:I200 = 1))
    3. MsgBox Application.WorksheetFunction.SumProduct((Range("K20:K200") = 1) * (Range("H20:I200") = 1))
    4. MsgBox Application.WorksheetFunction.SumProduct((Range("K20:K200").value = 1) * (Range("H20:I200").value = 1))

    but dont work anything!
    I think i will have to compare all rows one on one

  • Sumproduct In Vba

    Re: Sumproduct In Vba


    It seems Ok to me too, but not running. Yes i also tested to force to calculate.

    Code
    1. Range("A1").calculate

    , but... some more suggestions?

  • Sumproduct In Vba

    Re: Sumproduct In Vba


    Not running... And i cant understand the usage. Is not:

    Code
    1. Cells(1, 1).Formula = "=SUMAPRODUCTO((K20:K200=1)*(H20:I200=1))"
    2. Application.Evaluate ("A1")

  • Find Text Based On Active Cell Contents

    Re: Find Text Based On Active Cell Contents


    You have to put a commandbutton. Then right click->view code on the created button, and then you will see the code windows, and some code that says:

    Code
    1. Private Sub CommandButton1_Click()
    3. End Sub


    You only have to put in this Sub whatever you want to do. For example

  • Sumproduct In Vba

    Hey guys,


    I have to put from vba code the formula: =SUMPRODUCT((K20:K200=1)*(H20:I200=1)), in every sheet, for getting the result, and remove the formula. Getting and removing is ok : D , but i have some problems putting the formula.
    First i tried

    Code
    1. Cells(1,1).Formula = "=SUMAPRODUCTO((K20:K200=1)*(H20:I200=1))"


    I think is correct, but returns an error. What is more strange, that if i click on the cell and press enter, then the formula runs. Anybody knows whats happening?
    Another try

    Code
    1. Application.WorksheetFunction.SumProduct((Range("K20:200") = 1) * (Range("H20:I200") = 1))


    Code
    1. Application.WorksheetFunction.SumProduct(K20:200 = 1) * (H20:I200 = 1))


    Both of them give me errors.


    Help please

  • Insert Worksheet With Worksheet_change Event Code

    Hi,


    I have created a Workbook with a master sheet, where there is a summary of the other sheets. Every sheet has its own Worksheet_Change function implemented.
    Now, I want to create from a master sheet, all the other sheets. No problem if i only want to create sheets and put data in cells, but no manner to create its own Worksheet_Change function from vba code. I dont know if anybody would understand what i mean...


    Suggestions please?

  • Protect Userinterfaceonly When Shared

    Hi,


    I have a workbook that needs to protect & unprotect all sheets with the same pass. Running OK.
    I put some data in locked cells with a macro, ok when userinterfaceonly := true.
    I need to share the workbook... thats a problem.
    I found that userinterfaceonly only runs when i protect now, but if i close and reopen, userinterface turns to false. Well, the users that will use this workbook dont have to protect & unprotect, & dont know the password. To make the userinterfaceonly be true always, i can put in openworkbook event that it turns to true, but i need the pass to protect & unprotect is always the same... Not at all my best solution i think... Ok, I accept this way of protecting, but... when the file is shared... I cant protect or unprotect the file, so i cant turn to true userinterface!
    Any solution?

  • Display Print Password As "*"

    Re: Display Print Password As "*"


    I can´t see the vba code of your file. I suppose you are showing a userform, with a textbox where the user have to put a password, doesnt it? If it´s this way, you have to click right on the textbox, and click properties. There is property that says "PasswordChar", and if you enter the character you want, the text you enter in that textbox will be substituted by this char.
    If it´s not your problem, im sorry you would have to explain better.