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.

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

With frmClientSearch.lstJobs
        
        'arrActiveJobs is an array of row numbers containing job information
        For k = 0 To UBound(arrActiveJobs)
            
            rRow = arrActiveJobs(k)
            
            
            If k = 0 Then
                'Add the top row and populate with column headers
                .AddItem
                .List(k, 0) = tblData.HeaderRowRange(, Tools.Find_Column("ID", tblData.Name))
                .List(k, 1) = tblData.HeaderRowRange(, Tools.Find_Column("Date Created", tblData.Name))
                .List(k, 2) = tblData.HeaderRowRange(, Tools.Find_Column("Placement Type", tblData.Name))
                .List(k, 3) = tblData.HeaderRowRange(, Tools.Find_Column("Job Type", tblData.Name))
                .List(k, 4) = tblData.HeaderRowRange(, Tools.Find_Column("Allocated to", tblData.Name))
                .List(k, 5) = tblData.HeaderRowRange(, Tools.Find_Column("Notes", tblData.Name))
            Else
                .AddItem
                .List(k, 0) = tblData.DataBodyRange(rRow, Tools.Find_Column("ID", tblData.Name))
                .List(k, 1) = tblData.DataBodyRange(rRow, Tools.Find_Column("Date Created", tblData.Name))
                .List(k, 2) = tblData.DataBodyRange(rRow, Tools.Find_Column("Placement Type", tblData.Name))
                .List(k, 3) = tblData.DataBodyRange(rRow, Tools.Find_Column("Job Type", tblData.Name))
                .List(k, 4) = tblData.DataBodyRange(rRow, Tools.Find_Column("Allocated to", tblData.Name))
                .List(k, 5) = tblData.DataBodyRange(rRow, Tools.Find_Column("Notes", tblData.Name))
            End If
            
        Next k
    End With

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.

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.

Sub cmdSavetime()


    Dim tblData As ListObject
    Set tblData = Worksheets("tbl_data").Range("data").ListObject


    'you can then
    Set newRow = tblData.ListRows.Add
    'or something like this for an existing cell based on the current selection
    Set activeRow = tblData.ListRows(ActiveCell.Row - 1)


    'and populate with
    newRow(12) = cbT2.Value
    newRow(13) = cbT3.Value
    newRow(14) = cbT4.Value
    newRow(15) = cbT5.Value
    newRow(16) = cbT6.Value


End Sub

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

Select Case WorksheetFunction.DMin(tblActions.Range, "rag", "deadline=" & StartDay + cell.Value)
                    Case Is = 1
                       'Red
                        cell.Style = "Accent2"
                    Case Is = 2
                       'Amber
                        cell.Style = "Accent6"
                    Case Is = 3
                       'Green
                        cell.Style = "Accent3"
End Select

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

Re: Excel Not Responding

Hiya,

Just in case anyone is suffering the same issue, I've been researching the FM20.dll error and I've come across this thread. I've tested with adding userforms and it seems to present the same symptoms.

Link to Microsoft Forum

Re: Excel Not Responding

Just to add to this the file size is 106KB, figured I'd mention this to prove its not down to mass formatting or anything like that.

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:

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

Re: VLookUp Help

Maybe the use of vba is overkill here...?

Have you tried these?
https://www.pryor.com/blog/cop…as-down-to-fill-a-column/

Re: Assigning Values to Class Properties

To save having a line for each Let procedure, does anyone know if you can loop through properties in a class???

For i = 1 to Class.Properties.count
'do this
Next i

Re: Assigning Values to Class Properties

For those who view this in future, see Cytop's response in a duplicate thread (http://www.ozgrid.com/forum/showthread.php?t=197527).

Using the Class_Initialize sounds good. I suppose I could call each Let procedure and pass a different Origin variable from there. I'll give it a try!

Thanks Cytop and apologies for the duplicate thread.

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.

Dim pAppID As String


Property Let AppID ()
Origin =  "Application ID"


AppID = Sheet1.usedrange.find(Origin).Offset(0,1)


If AppID = "" Then
      Err = 1
End AppID


pAppID = AppID


End Property




'I don't want to use the following in another module, is this possible?
AppID = Sheet1.usedrange.find("Application ID").Offset(0,1)

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?

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.