Posts by holdec03

    Hi all, thanks for looking in!


    I'm writing a front end back end type database app using VBA (we don't have MS Access at work or a bespoke piece of software).


    On opening the workbook the following code runs.


    Code
    Public wbBackEnd As Workbook
    
    
    Sub OpenBackEnd
    Set wbBackEnd = Workbooks.Open Range("Back_End_Location")
    End Sub


    I want to keep wbBackEnd available to subsequent macros all the time the workbook is open. At the moment I am having to reinitialize it when I need it which I don't want to do, is there a way around this?

    Hi, thanks for your responses.


    Bob, it was all there, just not super clear, have now edited my original post for clarity.


    darkorder, I'm not using UserForm_Initialize() at all... My code for populating the listbox is below and was working perfectly up until we were upgraded (and relevant excerpt is below).


    Once populated, its just a case of frmClientSearch.show


    Hi All,


    I'm sure this has come up before but I wasn't able to find a topic, so sorry if this is a duplicate.


    My work have only just upgraded to Office 2016 and all users of a Macro I have written are experiencing the same issue (20 devices).


    During the macro a number of values are written into a multi row, multi column listbox which is contained within a userform. This userform is then shown to the user to make a selection.


    Since the upgrade the listbox is not showing its contents. After having populated it, I can retrieve every item in every row and column (using code below) but when the form is shown to the user it is simply blank.


    Code
    Debug.Print listbox.List(1,1)


    I've tried creating a new listbox which does the same thing, I've checked the properties for the listbox which haven't changed.


    I really don't know what is causing this, any assistance will be greatly appreciated.


    Thanks


    Connor

    Hi Nyatiaju,


    Thank you for your response but I notice that you have added the criteria to a worksheet and referenced the cell range containing this criteria. I was hoping not to have to do this if I can avoid it. Are you aware of a way to program the criteria in VBA?


    Also I note your Case Else suggestion to pick up the error, I may well have to take this route but was hoping to be able to identify specifically when an error is created?


    Thanks again!

    Hiya,


    You are adding 1 to lastrow which will be adding a new row below lastrow each time you run this.


    I assume for example you want the whole routine to fill in row x depending on which is selected at the very start? Can you clarify exactly how you decide which row to populate?


    Have you ever tried using tables? I find this Link very useful.



    PS. Don't forget to use code tags

    Hi All,


    Sorry if this has come up before, but I wasn't able to find a post.


    I have a table of data containing a column of various dates (deadlines) and another column with different numbers (which correspond to RAG status). I want to return the smallest number on a given date using VBA.


    My company still uses Office 2010 so I can't use MINIF. See my attempt below, I think its fairly self explanatory.


    I get the error "unable to get DMin property of worksheetfunction class" on the first line of the select.


    I also need to add an error trap in case the date is not found in the table, any ideas on best way to do this (like Case Is Error)?



    Any help is appreciated, thanks in advance!

    Hiya,


    Further update to this with at least a temporary solution as suggested on the Microsoft forum (link below). Simply delete the file referred to below (of course make a backup first):


    C:\Users\ (select user) \AppData\Roaming\Microsoft\Forms\EXCEL.box


    I have tried this with 3 users which seems to have solved the issues so far, though I will not consider this resolved until we've been a few days without any errors.


    Link to Microsoft Forum

    So I thought I'd give an update for anyone following this issue.


    I've had the users experiencing the issue log into my device (I don't have any issues) and the fault seems to have followed them. So in our case I think that rules out office updates although in someone else's situation it would still be a good check.


    I've had an update on the Microsoft forum quoted above (link again below for convenience) where another user PDE14 has been able to find an issue with FM20.dll being initialised twice. I'll let you read his post for a full explanation.


    I've passed this on to our IT department and will report back with any action taken.

    Re: Excel Not Responding


    I've got some screenshots of their update history, do you know if there's a quicker way? an export to excel maybe?


    If not I'll got through manually but there seems to be quite a lot going on in there.

    Re: Excel Not Responding


    Hi Rory,


    Thanks for your response, our IT department reinstalled office on one the devices and that didn't work so I'll suggest the other things you mentioned.


    We all have Office Professional Plus 2010 Version: 14.0.7184.5000 (32-Bit). That includes those experiencing the issue and myself who (at present) remains unaffected.


    Thanks Again

    Hi All,


    This is a bit of an unusual one, so I apologise for the poor title and if this is in the wrong part of the forum.


    So I have created a spreadsheet containing a number of different macros, it's being used more or less as a front end for a database. This document is used by 24 of my colleagues all day every day and has been performing smoothly until recently.


    All of a sudden (with no changes being made to the document) 3 of my colleagues have major issues with this document and everyone else remains unaffected. The issue occurs at different times as I will go on to explain but excel decides to completely crash (not responding) it then closes and tries to recover the workbook as you'd expect. This sometimes happens shortly after opening with no VB running at all or sometimes when running a particular piece of code.


    I've been in touch with my companies IT dept to try and find the cause but I'm starting to wonder if it could be something to do with the document? I've tried to take a look at task manager and it doesn't appear to be short on memory (even when running the macros that crash excel).


    *Edit*
    I've just had some feedback from our IT dept who are telling me that a file called FM20.dll is causing excel to crash. I'm just googling this now to find out how to deal with it, but I've not had any experience with it before.


    I'm hoping someone may be able to point me in the right direction to help me trouble shoot this bizarre problem. As always any input is greatly appreciated.


    Thanks in advance


    Connor

    Re: Convert Text to Number


    Hi,


    Thanks for the suggestion, I tried building into my code which didn't work so tried yours exactly still to no avail.


    I've realised I didn't include in my original post a sample of what is in the cell "£373.51", not sure if the £ will make a difference here?


    Thanks again

    Hi,


    I have a small green triangle in the top corner of my cells as I'm sure you are familiar with. Hovering over the exclamation point reveals that there is a number stored as text in the cell.


    I want to loop through columns in a report and reformat any columns containing this error programmatically.


    Having done a few searches I can't seem to find an answer that resolves this using VBA.


    Among other attempts, I have tried:

    Code
    .NumberFormat = "General"
    .Value = .Value


    I've tried recording the 'Convert to Number' button but this doesn't record anything...


    Your help is appreciated, thanks for taking the time to read.

    Hi,


    I have a class 'CApplication' which will take its properties from certain cells on a worksheet. I am hoping to make the Let procedure something like the below, is it possible for a class property to set its own value without providing it another procedure somewhere?


    I have tested something similar to this within the 'Get' procedure which works but I want to test the variables at an earlier point as the go into the class, to get all the errors out of the way.



    The reason I want to do it this way is that I have lots of variables that I'd like to test in this way, and by setting it up like this I only have to change the origin variable.


    I can then use the class later when I output the values on the sheet.

    Hi,


    I have a class 'CApplication' which will take its properties from certain cells on a worksheet. I am hoping to make the Let procedure something like the below, is it possible for a class property to set its own value without providing it another procedure somewhere?


    Code
    Dim pAppID As String
    
    
    Property Let AppID ()
    AppID = Sheet1.usedrange.find("Application ID").Offset(0,1)
    
    
    If AppID = "" Then


    I can then test the returned value and return errors accordingly.