Posts by Yzerman19

  • Activate Cell After Unloading Userform Without Clicking On It

    I have a userform in which the user makes an entry into a text box. A countdown of remaining characters allowed is displayed as the entry is made. When finished a commandbutton is clicked launching the following code:


    What should happens is the userform unloads and the user can immediately make an entry into an activecell. The problem is that the user must click on the cell or the Application Title Bar to activate Excel to make the entry. It is annoying and I can't figure out how to correct it.


    Okay, I found the problem. I changed the userform ShowModal property to False. Now when the userform closes the worksheet gets focus. I don't understand why, but it worked.

  • Custom Toolbar/Commandbar For Template

    Re: Deleted Toolbars Reappear


    I believe that you after you set the toolbar the way that you want to, you need to go to:
    Documents and Settings>Username>Application Data>Microsoft>Excel


    Copy or Move the .xlb file you see there into the XLSTART folder.


    Now when you start excel it will load with those settings.

  • Prevent Combobox Change Running Other Control Event Procedures

    Re: Prevent Combobox Change Private Sub From Running


    I'm having trouble understanding where to use the code since I'm not all that familiar with it.


    Here's some code from the two private subs that are involved with my problem:



    The other privatesub is:

  • Prevent Combobox Change Running Other Control Event Procedures

    I have a userform with a combobox in it that's rowsource is a column of company names in a worksheet. When a company name is chosen, a combobox change private sub runs and many userform textboxes are populated with information about the company that was chosen from the combobox dropdown. This information is stored on a worksheet. I then want to edit any of the information in these textboxes. Once my edits are made I have a CommandButton that is pressed to save the edit changes. This CommandButton runs a private sub that disables the combobox (thinking this would prevent the combobox private sub from running), deletes the row that the information originated from, and then SHOULD make a new row of values based on the contents of the textboxes following the edits. The problem is that the CommandButton coding that deletes the row causes the combobox change private sub to run because the company that had been selected is now the missing from the rowsource; this causes an error. How do I prevent this from happening?

  • Size UserForm For Different Computers

    Re: Size UserForm For Different Computers


    Okay, I found the problem. The DPI settings on the work monitor were set at 120 (large) instead of 96 (standard). So now that the settings have been reset to 96, I have reconfigured the dimensions of my labels and the logo and everything looks correct.

  • Size UserForm For Different Computers

    Re: Get UserForm To Keep Its Appearence On All Computers


    The only code to set the appearence on load is

    Code
    Me.Height = Application.Height
    Me.Width = Application.Width


    Controls are created at design time.
    Cropping made the image look different in size. The form is designed to load as Windows default in the property settings.

  • Size UserForm For Different Computers

    Hello Everyone,


    I've created a userform that will be used by multiple users. I'm not finished coding it yet but when I tried to work on it on my computer at home it did not look the same as it did at work. I tried it on several other computers and experienced the same problem.


    It appears that the inserted company logo is zoomed in and the some of the label text has wrapped and is not visible.


    I've attached a screen shot of the form on my work computer and the way it looks on other computers.


    Any help coding this to maintain its original appearence on all machines would be most appreciated.

  • Compare Cells Across 2 Sheets & Insert Rows If Criteria Met

    Re: Compare Cells Across 2 Sheets & Insert Rows If Criteria Met


    The solution was to create a column next to the column containing the countif statements. This new column has an if statement giving a value of 'Y' if the countif value is greater than 1. My macro then searches down that column for the first Y it comes to, activates the cell it finds a Y in, deletes the Y, looks at the value in the column telling how many asset occurences on the work order sheet and copies that row and inserts a number of copied rows based on the value - 1. The process begins again and knows how many times to do it based on the number of 'Y's in the IF statement column at the beginning of the process.

  • Compare Cells Across 2 Sheets & Insert Rows If Criteria Met

    I have three worksheets that I am combining into one master worksheet. The data from Worksheets 1 & 2 are listed combined under the same headings onto the master worksheet. I have three additional headings on the master that need to populate information from Worksheet 3 under them. I would use VLOOKUP to populate those fields if it weren't for the problem that some asset numbers have more than one entry. The request I received is to insert copy the criteria row below it so that there are an equal number of entries for each occurance of information from Worksheet 3 to copy that data to.


    (example = Asset 12345 has 3 different work orders on Worksheet 3, therefore I have three rows for Asset 12345 on the master with the data I need copied under the 3 headings.)


    To figure out how many rows to insert I created a last column (that I will delete at the end of the macro) that uses COUNTIF to count the number of occurences of the asset number on Worksheet 3. I want my macro to look down this column and for every value over 1 add rows equal to that value-1 beneath the row. I then need to copy the information from the criteria row into the newly inserted rows. I will need to figure out how to populate the data from worksheet 3.


    Any suggestions. I tried recording a macro of me using FIND >1 values while selecting the column containing criteria and it didn't work because it likely was literally looking for ">1".


    I'm stuck and getting a headache because I can't find any answers online.


    Thanks.

  • Prevent Column From Sorting

    Re: Prevent Column From Sorting


    Thanks Dave. But seriously, I work with some folks that are not the sharpest knives in the drawer. There is a report that has asset numbers that combine text and numbers (ex. 12345FM) and asset numbers that are strictly numberical. I have created a column that extracts only the numerical information from that asset column in order to sort the list. I've even labelled the column 'SORT' so as to make it obvious, but there will always be that one individual that will attempt to sort by the 'ASSET' column. So what I want is for when they are choosing the columns they would like to sort by to not have that 'ASSET' column available in the dropdown list within the SORT form. I'm guessing this is not possible?

  • Collect Month, Year & Day For Print Header

    In a macro that I run, I have three input boxes to collect values that help create the filename when the workbook is saved at the macros completion. I would like to use those values to create a header in page setup using vba. I can get it to use one of the values, but when combined I do not get the desired result. My coding is off. Here is the relevant code that I am using:


    Code
    saYear = InputBox(Prompt:="Enter the year (yyyy) of this report.")
    saMonth = InputBox(Prompt:="Enter the month (mm) for this report.")
    saDay = InputBox(Prompt:="Enter the day (dd) for this report.")
   
    Sheet1.PageSetup.CenterHeader = _
    "&""Arial,Bold"" &16Report Data extracted from system as of " & saMonth - saDay - saYear


    The header shows the correct text but the date that I want displayed is a single number because the 3 values are subtracting themselves from each other. I tried inserting quotation marks around the hyphens but the code was a compile error: Expected end of statement


    The result I want to read is:
    Report Data extracted from system as of MM-DD-YYYY


    What code should I be using to get my desired results?


    Thanks.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Nevermind, I figured it out.
    Code was:

    Code
    Sheet1.PageSetup.CenterHeader = _
    "&""Arial,Bold"" &16Report Data extracted from system as of " & saMonth & "-" & saDay & "-" & saYear


    Thanks.

  • Set Page Breaks Based On Cell Value On Protected Sheet

    Re: Set Page Breaks Based On Cell Value On Protected Sheet


    I figured it out. Any of the pagebreak code such as

    Code
    .HPageBreaks(1).Location = Range("A47")

    was causing the code to stop running. When I instead used

    Code
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Range("A47")

    it worked.


    Thanks again for looking into this for me Dave. Have a great weekend.

  • Set Page Breaks Based On Cell Value On Protected Sheet

    Re: Set Page Breaks Based On Cell Value On Protected Sheet


    Thanks for showing me how to shorten my code. But for some reason the page break setting is still causing the code to stop. I have added ON ERROR RESUME NEXT at the beginning to bypass the problem for now until I can figure out what is going wrong.

  • Set Page Breaks Based On Cell Value On Protected Sheet

    Okay, I'm not sure went wrong but my code was working fine until I protected the worksheet. Then I made some changes to the code by adding code to unprotect the sheet before running the code and then protecting it again when complete. That didn't work. I unprotected the sheet and ran the code again and now I'm getting a Run Time Error and the code stops when it attempts to set the Page Breaks which was previously fine. Because the data I use to generate this report is based on data from another report that does not need to recalculate before running. I have a Worksheet Activate Private Sub that sets the Application Calculation to Manual. When the sheet is deactivated it sets it back to Automatic. Hence the line in the following code to calculate this worksheet. When attempting to debug the line that highlights is the first line that attempts to set the page break that corresponds to the matching location criteria from cell A2.
    Here's my code:


    Is there something wrong with this?

  • Concatenate Cells To Create Named Range For Formula / Function

    I'm trying to concatenate text with different cell references within my workbook that when combined create the name of a range I have within the same workbook. I then will sum a sumrange, that is also a concatenation of text and cell references. I'm getting nowhere fast. When I type the name ranges into the sumif formula everything is great. When I create those same name ranges using concatenation it all falls apart. I'm guessing SUMIF and concatenation can't play nice together. I've also tried vlookup with sumif and didn't get anywhere.


    Example:
    =SUMIF(CONCATENATE("CS",YMKEY,"KEY"),CONCATENATE($A6,"TX",IF(YMKEY="Y","Y",MonthNum)),CONCATENATE("Data!MITSUBISHI1IND",YEARNUM)


    References:
    YMKEY = "Y" or "M"
    $A6 = the name of a county
    MonthNum = the number of the month (1 - 12)
    YearNum = the year using four digits (i.e. 2008)


    The ranges attempting to be referenced by the concatenation:
    CSMKEY, CSYKEY, Data!Mitsubishi1IND2007, Data!Mitsubishi1IND2008, etc.


    Any suggestions?