Posts by richadj4

  • Paste method of Worksheet class failed

    Re: Paste method of Worksheet class failed


    sorry, the above was to PUT things in the clipboard, this is to get them from the clipboard:

    Code
    Function ClipGet() As String
'Get text from the clipboard and return as a string
    Dim doClip As DataObject
    Set doClip = New DataObject
    doClip.GetFromClipboard
    ClipGet = doClip.GetText
    Set doClip = Nothing
End Function

  • Paste method of Worksheet class failed

    Re: Paste method of Worksheet class failed


    That's generally a bad way to do things, but it does appear to work for me.
    When I need to use the clipboard I generally use this sub:

    Code
    Sub ClipPut(ByVal Value As String)
'Send a string to the clipboard as text
    Dim doClip As DataObject
    Set doClip = New DataObject
    doClip.SetText Value
    doClip.PutInClipboard
    Set doClip = Nothing
End Sub


    *note: require the msforms reference

  • Loop through Drop down list, copy and paste range values, and saveAs new workbook

    Re: Loop through Drop down list, copy and paste range values, and saveAs new workbook


    Then within the for loop, you want to create a new workbook, "copy" the information in the range into it, save it and close it. kinda like so


    Code
    set newWB = workbooks.add
newwb.sheets("Sheet1").range("$E$2:$U$184").value = thisworkbook.sheets("Sheet1").range("$E$2:$U$184").value
newwb.saveas c.value
newwb.close false

  • Loop through Drop down list, copy and paste range values, and saveAs new workbook

    Re: Loop through Drop down list, copy and paste range values, and saveAs new workbook


    okay,


    1) cycle through a dropdown list. Your dropdown list (data validation) is reading from a range, hopefully it's a named range, but I'm going to assume from the above that its not. Basically you need to iterate through that range, and set g3 to each member of that range. Kinda like so


    Code
    dim rdata as range
dim c as range
set rdata = thisworkbook.sheets("Sheet1").range("$Z$1:$z$20")   'Save the range the dropdown data is in


for each c in rdata.cells
   thisworkbook.sheets(1).range("$g$3").value = c.value
next

  • Open workbook in VBA (readonly) suppressing all macros and calculations

    Re: Open workbook in VBA (readonly) suppressing all macros and calculations


    enableevents stops EVENTS, so it would stop events running in procs.xlsm, but not code, so if procs.xlsm has code to trigger on a worksheet change event or anything like that, then it would not function correctly, otherwise it would be fine


    calculationmode is one of those things I HATE because I don't understand it well enough. I don't BELIEVE you can turn off calculation by workbook as it is an application property, however you CAN turn off calculation by workSHEET by setting the enablecalculation property on the worksheet. In theory if you set Application.Calculation = xlCalculationManual, open the workbook, loop through each sheet and set the enablecalculation property to false, and then re-enable global calculation then you SHOULD get calculation only working in procs.xlsm. probably.


    HTH

  • error 400

    Re: error 400


    code works on my machine. I would SUSPECT that you are trying to select something you can't, ie a hidden cell. I would step through the macro 1 line at a time and see what it should be selecting when you get the error.


    FYI - You should not be using select, but that's a whole 'nother conversation

  • Having conditional formula problems using dates

    Re: Having conditional formula problems using dates


    The example shows your existing data well, but does not provide an example of what you actually require!


    What exactly do you want to highlight? a full row? or specific columns?
    I'm assuming that whatever needs to be highlighted should be highlighted when the date in "open date" is between 23 and 30 days prior to today (possibly a different colour after 30 days?) and ONLY if Close date is blank. Is that correct? or have I missed something?


    In general with conditional formatting I find it easiest to "work out" the appropriate formulae by using them actually in the table to create a column of True/False for testing, and then when you are confident they are correct, moving them from the table to a conditional format rule.
    looking at the existing conditional formula in the example, I would suggest it be changed to:
    =AND(TODAY()-$K5>23,TODAY()-$K5<30,$M5="")


    HTH

  • Macro protection using information from external file

    Re: Macro protection using information from external file


    There is possibly a better way to do this, but what I have done in the pass is:


    Create the addin with the appropriate code (as PUBLIC functions or subs) save in an appropriate place
    Ensure the addin VBAProject has a unique name (in the project explorer, select the project and in the properties window rename it myaddin1)
    From the actual workbook, create a reference to the addin (tools-References-Browse-Change the files of type to excel files-locate the addin)


    Now when the workbook is opened, the addin will also (invisibly) be opened, and from your workbook, code like myaddin1.functionname will work as long as the function is public

  • Macro protection using information from external file

    Re: Macro protection using information from external file


    cytop is ENTIRELY correct. Excel is crap at security. There are a couple of options that I have used in the past, that MAY work depending on your specific situation.


    rather than having a txt file "somewhere", I would suggest actually placing THE CODE somewhere else. If your code is in an addin in an external workbook, then someone who illegitimately "borrows" the actual workbook will end up with nothing but function descriptions/addin pathname.


    You still have the (unsolvable) problem of someone taking the addin with the code, but if this is housed in a different directory then the casual user would not even know it existed.


    Obviously not 100% (nothing is with excel), but I have used this in the past where the concern was with a low level user passing the spreadsheet to someone external to the company. Management were confident that no-one within the company was BOTH IT-literate enough to break the simple VBA password (in order to determine the location of the addin) AND a security risk, they were more concerned with "peons" just copying the file and sending to someone with more knowledge.


    Not sure if this helps at all.

  • VLOOKUP Exchange Rate from 12 different Workbooks Depending Upon Month Column

    Re: VLOOKUP Exchange Rate from 12 different Workbooks Depending Upon Month Column


    hmmmmmmm, actually harder than I thought with vba :(


    After several attempts, I can pass an external reference into worksheetfunction.vlookup in vba, which is very frustrating.


    The best solution I have been able to come up with involves using a cell somewhere in the workbook, ideally on a hidden sheet, but a hidden column/row, or just off to the far right would work.


    Firstly select this cell and "name" it "temp" (you name a cell by typing the name in the box directly above the "a" label for the first column)


    Then in a VBA module enter the following:


    Code
    Public Function ExtVLookup(dValue, iCol) As Variant
Const sFolder As String = "d:\temp\"  'This needs to be the folder the 12 workbooks are in
On Error Resume Next
ThisWorkbook.Names("temp").RefersToRange.Formula = "=Vlookup(" & dValue & ",[" & sFolder & Format(dValue, "mmmm") & " Prices.xlsx]Master table prices " & Format(dValue, "mmmm yy") & "'!$A$3:$F$23," & iCol & ",FALSE)"
ExtVLookup = ThisWorkbook.Names("temp").RefersToRange.Value
ThisWorkbook.Names("temp").RefersToRange.ClearContents
End Function



    Then in your worksheet, you will be able to use = ExtVLookup(A24, 5) to return the appropriate result.

  • Index match based on 2 criteria

    Re: Index match based on 2 criteria


    Your example does not seem to make a lot of sense.
    your "What I have" has some issues with it, but more relevantly your "what I want" does NOT seem to be related to b2 as your description states?


    The "what I want" seems to imply that what you are looking for is something like "for each SKU list in columns all entires relating to that SKU and the keyword "lost" in column "E". This is certainly possible, but I don't understand what (if any) relevance there is in column B

  • VLOOKUP Exchange Rate from 12 different Workbooks Depending Upon Month Column

    Re: VLOOKUP Exchange Rate from 12 different Workbooks Depending Upon Month Column


    Quote from danerida;716999

    Are your workbooks always named the same way? if so, you could try something like:
    =VLOOKUP(A24,"'["&TEXT(A24,"mmmm")&" Prices.xlsx]Master table prices "&TEXT(A24,"mmmm yy")&"'!$A$3:$F$23",5,FALSE)


    I'm almost 100% sure this won't work. in your function the second parameter is a string, not a range. the ONLY way to reference a range via string is with indirect, which won't work inter-workbook.


    I've just tried a version of this, and it just returns #Value! If you have anything resembling a working example I'd be thrilled!
    (really hoping to be proved wrong here)