Posts by skepticologist

I have the following formula in a workbook, using the INDIRECT function to refer to another workbook named "2014 Tax Year PACs.xlsx". INDIRECT is pointing to a cell in the first workbook with the current tax year so I don't need to edit formulas each time the tax year changes.

Following is the INDIRECT function as it appears in the cell in the first workbook mentioned above: =COUNTIF(INDIRECT("'[" & UT_current_tax_year & " Tax Year PACs.xlsx]Sheet1'!$D:$D"),">""")-1. It returns an accurate count of entrties in the second workbook with no indications of any errors.

However, when I insert the formula into the same cell using VBA, with the only changes being doubling up on interior quotation marks, I get a runtime error 1004: "Application-defined or object-defined error." Below is the formuia as it appears in the line of code meant to insert it into the cell in the first workbook:

Selection.Formula = "=COUNTIF(INDIRECT(""'["" & UT_current_tax_year & "" Tax Year PACs.xlsx]Sheet1'!$D:$D""),"">"""")-1"

This one has me stumped, so I'd really appreciate some help.

Re: Make Userforms Go Away

Thanks for all the useful suggestions. I set up a test routine so I could see what's going on with no extraneous stuff, and incorporated each of your suggestions in different combinations, but the userform refused to disappear. I honestly don't think that's even slightly attributable to the quailty of your suggestions, and I'm certainly no closer to determining what's really csusing the problem. I can develop several applications incorporating userforms with absolutely no difficulty, and then the next one will behave just like the current one.

Given the absence of a viable alternative, I've resorted to building my own forms using a rounded rectangle and ActiveX controls. This approach takes a bit of revision of my usual development plan, but it can be made to work by invoking whatever next step is appropriate in the code for the command buttons.

If anyone has other thoughts on this subject, I'd certainly welcome them.

Re: Make Userforms Go Away

Thanks so much for your interest. Yes, UserForm9 is the culprit in this case. It doesn't reappear. It just never goes away, which makes it difficult to know what the offending code might be. If you could provide a few guesses, I could experiment to narrow the problem down.

Making userforms disappear after they've done their job is becoming one of my all-time major challenges in Excel.

The most recent example is an application that so far has 8 userforms, each of which is invoked based on conditions found by formulas during execution of a "master" macro. I've experienced the same problem, i.e. unresponsiveness to Hide and Unload statements, with most of them. I even tested a Cancel function, which of course involved hiding the userform, during each step of building the latest one, and everything checked out until I apparently broke some rule. So now, the only way I can get rid of it is to click on the Reset button in VBE.

I've searched Ozgrid, other forums, and the web in general looking for the secret. And, although I've found a lot of posts that address at least very similar problems, I've yet to stumble across it.

So if one of you kind and expert members could please check the code for my Cancel command button below, and let me know what the problem might be, it'll make my day and much more!

Private Sub CommandButton2_Click()
'
Dim FirstName As Range
Dim LastName As Range
Dim ImportName As Range
Dim FirstNameEdit As Range
Dim LastNameEdit As Range
Dim Option1SelLink As Range
Dim Option2SelLink As Range
Set FirstName = Sheets("UT").Range("UT_short_names_first_name")
Set LastName = Sheets("UT").Range("UT_short_names_last_name")
Set ImportName = Sheets("UT").Range("UT_short_names_import_name")
Set FirstNameEdit = Sheets("UT").Range("UT_short_names_first_edit")
Set LastNameEdit = Sheets("UT").Range("UT_short_names_last_edit")
Set Option1SelLink = Sheets("UT").Range("UT_mult_inst_opt_btn_sel_1")
Set Option2SelLink = Sheets("UT").Range("UT_mult_inst_opt_btn_sel_2")
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    UserForm9.Hide
    FirstName.Formula = "=IF(UT_short_names_row>"""",INDEX(HD_HRc_data,UT_short_names_row,4),"""")"
    LastName.Formula = "=IF(UT_short_names_row>"""",INDEX(HD_HRc_data,UT_short_names_row,3),"""")"
    ImportName.Formula = "=IF(UT_short_names_row>"""",RIGHT(INDEX(HD_HRc_data,UT_short_names_row,2)," & _
        "LEN(INDEX(HD_HRc_data,UT_short_names_row,2))-FIND("":"",INDEX(HD_HRc_data,UT_short_names_row,2))-1),"""")"
    FirstNameEdit.ClearContents
    LastNameEdit.ClearContents
    Option1SelLink.Value = False
    Option2SelLink.Value = False
    Label4.Visible = False
    Label5.Visible = False
    TextBox4.Visible = False
    TextBox5.Visible = False
    CommandButton1.Top = 144
    CommandButton2.Top = 144
    UserForm9.Height = 200.4
    UserForm9.Hide
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    
End Sub

Re: Using Concatenation in Path/Filename Formula References

Thanks, NB. I always have automatic calculation on but double-checked, and it is/was. Any other possible reasons you can see for the formulas not working?

I'm trying to write formulas to automatically retrieve information from files whose names will change over time, and depending on the user's selections. As a current example, I want to give the user the option (via combobox) to select a report for either the 4th quarter of 2013, or the 1st quarter of 2014. I've written formulas for both the path and file names with the file dates concatenated with references to formulas in other cells that contain the relevant (and changeable) years, dates, and or quarters. Two examples of those:

1. The file for 4Q13
Path: ="C:\\Updates to EPS\"&TEXT(UT_last_quarter_start_date,"yyyy")&" Updates\Q"&UT_last_quarter_numeral&" ~ "&TEXT(UT_last_quarter_start_date,"mmmm d, yyyy")
Filename: ="GSD_Current Q"&UT_last_quarter_numeral&" "&TEXT(UT_last_quarter_start_date, "yyyy")&".xlsx"
1. The file for 1Q14
Path: ="C:\\Updates to EPS\"&TEXT(UT_new_quarter_start_date,"yyyy")&" Updates\Q"&UT_new_quarter_numeral&" ~ "&TEXT(UT_new_quarter_start_date,"mmmm d, yyyy")
Filename: ="GSD_Current Q"&UT_new_quarter_numeral&" "&TEXT(UT_new_quarter_start_date, "yyyy")&".xlsx"

I can then dim the value returned by the formula as a String and use the following code to open (for example) the file:

Application.Workbooks.Open (GSDPriorFilepath & "/" & GSDPriorFilename)

- or -

Application.Workbooks.Open (GSDCurrentFilepath & "/" & GSDCurrentFilename)

And if the user selects a different quarter for his or her report, the formulas change values just like they're supposed to, and open the appropriate files.

The problem I'm having is with getting worksheets formulas (not code) to work with the references to the path/file names.

As an example: =MATCH(UT_GM_host_country,'[&UT_GSD_Current_last_filename&]Singapore'!$B:$B,0)

The formula works fine when I initially write it, correctly returning the row # of the UT_GM_host_country-referenced workbook. But if I change the reference from _last_ to _new_, it continues using the initial reference. It doesn't matter what I change in either of the workbooks: the value returned stays the same.

I'm beginning to think I'm trying to do something with formulas that only code will allow me to do, but they do - at least initially - work fine. I'd very much appreciate someone letting me know whether it's possible to do what I'm attempting, and if it is, tell me what I'm doing wrong.

I've lost track of how many hours I've spent on this site and others in an effort to discover the secret of formatting numbers in userform textboxes. I've tried every suggestion I've found with no success. Some of the textboxes I use will be locked and will never be changed by the user. Their only function is to display the value in the ControlSource cell for reference. Of course, other textboxes will be used for input by the user. Very simply, I want them ALWAYS formatted as "#,##0.00", whch is the same format as the ControlSource cell.

Re: Stop Screen Flicker

Quote from maudibe;663116

Skept,

That is about as good as it gets. There are a couple of things to remember. Even if you left out the line to turn screen updating back on, at the end of the routine it defaults back on. Therefore, if you are running routines sequentially, each one must have the lines. The screen updates at the end of the routine so between routines the screen updates. However, if you are branching to another routine then back, only the main routine needs to have them. Suggestion would to combine routines into on larger routine if your situation warrants so there will be only one screen update

Maud

Thanks so much. I currently have nine routines called from a base routine, and some of them call other routines. All of them have both the lines. If I understand you correctly, only the base routine actually needs them. I'll try cramming all the code into one routine now that I've tested all the branched routines.

I've read everything I can find on this forum and am still getting significant screen flickering in an application I'm developing. What I've tried so far:

• Start each macro with
  
  Code

  Application.ScreenUpdating = False

  and end with

  Code

  Application.ScreenUpdating = True

  .
  

• Use Application.Goto Reference, With/End With,etc. to replace all possible .Select commands.

The worst flicker occurs when my code toggles shapes' .Visible property between True and False, but it happens in several other instances, as well. I'll very much appreciate any alternate methods.

I've been encountering a pesky problem for several years and have finally decided to try to resolve it. Occasionally, only on random sheets within a workbook, even the simplest formula is displayed in a cell, not the result. If I paste a similar formula from the same sheet into the cell, it shows the result as it should. But if I edit it to get the result I want, it goes back to displaying only the formula. I've developed a workaround that involves copying the formula from the formula bar, then pasting it into the formula bar with a different active sheet. Then I can copy it from the cell and paste it as a formula into the cell on the original sheet and finally get the result. Of course, all the range references need to be made absolute for this to work. Even after all that, something that I haven't been able to identify will cause the formula to revert to display only.

I've tried every permutation I can think of, including formats and F9, with no success to date. There appears to be nothing unique about the specific worksheets or the workbooks they're in.

This has happened with at least 3 versions of Excel, and I admit to being completely stumped. If one of you kind experts can let me in on the secret, it will be much appreciated.