Posts by GuyGadois

  • Reset UserForm Textbox Controls

    Re: Resetting Userform Textbox Values


    Quote from Dave Hawley

    You are setting the TextBox values via the Initialize Procedure. Why not simply use;

    Code
    TextBox1 = VbNullString
TextBox2 = VbNullString
TextBox3 = VbNullString
TextBox4 = VbNullString


    Dave thank you for your suggestion. I tried that code under Private Sub CommandButton2_Click() and Private Sub UserForm_Initialize() to no avail. Where do you suggest I put it?


    Thanks in advance,


    Guy

  • Reset UserForm Textbox Controls

    I have the following code that is not working properly. When the form loads it always shows data from the previous time the form loaded. I searched and found the following code which doesn't seem to be working correctly. I thought by unloading the userform the values of the textboxes would "reset". Not happening.


  • Copy Relative Formula & Use Row Reference From Variable

    I have the following code which Clears the content of a cell. The next thing I want to have happen is to have another cell equal a formula but make that formula relative to its place. The formula is the following


    =IF(ISERROR(VLOOKUP(F56,Routes_All,2,0)),0,VLOOKUP(F56,Routes_All,2,0))


    I am not sure how to paste it where the row changes depending on where it is pasted. Here is the code that does not work...



    As you can see, no matter where I paste it it will always refer to row "F". How can I have it refer to row "cRow"?


    Thank you


    Guy

  • Copy/Paste Range To Different Size/Shape Range

    Re: Copy/Paste Range To Different Size/Shape Range


    Dave thank you again for your help,
    I have a related question (let me know if it shoud be in a seperate post). What is the best way to handle copying ranges that are not the same row size. For example, in my workbook I have a range call Month_January where it is rows 3:34. The number of rows can be more depending on if a user adds rows in Janaury and then the range expands.


    Now when I do the following code (below) it writes over other months depending how long January is in the file the data is being copied from. Is it possible to expand the range of the destination to match the one being copied?


    Thank you,


    Guy


    Code
    wkbDataFile.Worksheets("2008 Log").Range("Month_January").Copy Destination:=wb.Worksheets("2008 Log").Range("Month_January")(1,1)

  • Copy/Paste Range To Different Size/Shape Range

    Re: Copying One Range To Another When They Are Different Sizes


    Quote from Dave Hawley
    Code
    wkbDataFile.Worksheets("Admin").Range("Admin_Goals_Actuals_Miles").Copy Destination:=wb.Worksheets("Admin").Range("Admin_Goals_Actuals_Miles")(1,1)

    Always paste to the top left cell of any range, that way the shape and size of the copied range doesn't matter.


    Dave, thanks for the suggestion - I didn't know that. I did try the suggestion but it still copies over both columns. Is this because I am going from 2 columns to one column? When I copy it over I only want to copy the first (left most) column.


    Guy

  • Copy/Paste Range To Different Size/Shape Range

    I have the following code that let's the user choose and "import" data to an existing sheet. It works well up until now. The problem is that the three ranges that I am trying to copy the data from on workbook to another has changed size. In previous version of my workbooks the range was two columns by 10 rows. Now, it is 1 column by 10 rows. So, when I run this macro it doesn't work because the two ranges are different. Is there any way to:
    1) Only copy over one of the rows of a range thus making the macro run?


    2) Do not run that part of the macro if there is an error?


    Thanks so much for reading this long-winded description but the error is a big problem for me.


    Thanks,


    Guy



  • Open Web Page Via Macro Code

    Re: Open Web Page Via Macro Code


    Quote from Dave Hawley

    ........[GS="Open web page"]*[/GS]


    Dave, thanks for the tip. I should have tried that one. I swear I tried a number of different searches and just didn't find what I was looking for.


    The common code that can be found on this site to open a webpage from a macro is:


    Code
    ActiveWorkbook.FollowHyperlink Address:="http://www.yahoo.com"


    It works fine on my PC's Excel 2003 but I get the following error using Excel 2003 on my Mac. The error is:


    Run-Time error '-2146697208 (-7FF3FFF8)':
    Cannot download the information you requested.


    Anyone come across this problem?


    Thanks,


    Guy

  • Hide / Unhide Columns On Protected Sheet

    Re: Hide / Unhide Columns On Protected Sheet


    Quote from Dave Hawley

    If it's not being set, then that is the problem. Anyway, as I said, why not use;


    Code
    With Sheet1
        .Protect Password:=SpreadsheetPassword, UserInterfaceOnly:=True
        .EnableOutlining = True
        .Range("column_calories").EntireColumn.Hidden = ToggleButton1.Value
    End With


    Dave,
    I presume that it's not being set because when I step through the macro and I hover over the "UserInterfaceOnly:=True" code I don't get any "pop-up" display. Maybe it is being set and I just am not sure how to verify it.


    The code you suggested above does work but I don't think it works in spreadsheet logic. The ToggleValue1.value is set when a form is submitted. The form is a preferences form that allows the user to hide and display certain columns. So, the only time I adjust the ToggleButton values is when I am loading the form.


    The Sheet protection is a default to go off when the spreadsheet opens (and the user enables Macros. If he/she doesn't enable macros then all sheets are hidden except a sheet displaying a warning). At no other time does the spreadsheet change the protection of a sheet.


    So, does it make sense to set the togglebutton values on startup? I don't think so because everytime the user changes the 'preference' form the ToggleButton Value may change the next time the form is loaded (because they may have hidden a column). I think the code below won't work because it sets the ToggleButton value at startup when I need it to set when the form is loaded.

    Code
    With Sheet1
        .Protect Password:=SpreadsheetPassword, UserInterfaceOnly:=True
        .EnableOutlining = True
        .Range("column_calories").EntireColumn.Hidden = ToggleButton1.Value
   End With


    In any case it seems like the real problem is that the 'UserInterfaceOnly:=True' code is not going off. Any reason why this code would not work if it goes off every time the workbook starts?


    Code
    With Sheet1 
    .Protect Password:=SpreadsheetPassword, UserInterfaceOnly:=True 
    .EnableOutlining = True 
End With


    Thank you for all your help. As you can tell I am just learning by the help of everyone here and by trial and error - mostly error.


    Guy[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I have adjusted the code of my spreadsheet but the reults are the same. When I try to hide/display columns I get the folloing error "Run-time error '1004': Unable to set Hidden property of the Range class". I thought I did this right, ensuring "UserInterfaceOnly:=True"


    Here is the new code I have:


    ThisWorkbook



    Now, that code is used to start up the spreadsheet. The following code is where the error is. It is a 'Preferences' UserForm where the user can specify whether columns are hidden. Here is the full code from the UserForm.



    Looking at this is there any suggestions on how to make this error go away?



    Thank you,


    Guy

  • Restrict Combobox Selection To List Only But Allow Deletion

    Re: Restrict Combobox Selection To List Only But Allow Deletion


    Quote from Dave Hawley

    I thought you wanted to allow deletion of any choice?


    Dave,
    This solution seems to work. A user can type in a valid option and then change that option without any error. The user is forced to only user valid combobox options. I thought I tested it multiple times but will do again to make sure the user can delete their entry and change it.


    Guy

  • Hide / Unhide Columns On Protected Sheet

    Re: Hide / Unhide Columns On Protected Sheet


    Quote from Dave Hawley

    Why aren't you specifying the sheet by CodeName in the code to hide/show columns? With so many sheets, how do you know the correct sheet is the active sheet when running the hide/show columns macro?


    Anyway, step through your code to ensure it IS setting the UserInterFaceOnly argument to True.


    Dave,
    When I step through the ShowAll macro it doesn't seem to be setting the UserInterFaceOnly argument to True. At least when I step through it nothing comes up when I highlight it.


    I try to be cognizant of the CodeNaming and thought I did this...


    Code
    With Sheet1
       .Protect Password:=SpreadsheetPassword, UserInterfaceOnly:=True
       .EnableOutlining = True
    End With


    The Form/Macro where I hide show columns look like this (I took out all the other hide/show columns)...


  • Hide / Unhide Columns On Protected Sheet

    Re: Hiding Columns On Protected Sheets


    Quote from Dave Hawley

    You open event cannot be firing off is my guess (as you only show part of the code and don't mention where it is). Why not simply put the UserInterfaceOnly into the macro to hide columns? Then simply use;


    Code
    Range("column_calories").EntireColumn.Hidden = ToggleButton1.Value

    Dave, thank you for your reply.


    I have this in ThisWorkbook



    If the userdoes NOT accept macros then only sheet 6 is shown (which gives the user a warning). If they accpt then ShowAll is run and the sheets are protected



    Is this not the place I should be putting the Sheet protection? Am I not doing this correctly?


    Thank you in advance.


    Guy Gadois

  • Hide / Unhide Columns On Protected Sheet

    When my spreadsheet opens it automatically protects the sheets using the following code:


    Code
    With Sheet1
       .Protect Password:=SpreadsheetPassword, UserInterfaceOnly:=True
       .EnableOutlining = True
    End With


    and I use the following to hide the column if the toggle button on my form is 'true'. The problem is it gives an error if the sheet is protected. it worksfine without protection.


    Code
    If ToggleButton1.Value = True Then
        Range("column_calories").EntireColumn.Hidden = True
        Else
        Range("column_calories").EntireColumn.Hidden = False
    End If


    Is there any way to hide a column on a protected sheet?


    Guy

  • Restrict Combobox Selection To List Only But Allow Deletion

    Re: Restrict Combobox Selection To List Only But Allow Deletion


    Quote from Dave Hawley

    It restricts users to choose from all but the 1st combobox item (listindex 0).


    OK, I think I understand. How would I adjust this so the only valid entry is the choices in the combobox (I assume that is thelistiondex). Sorry if I sound confused. I am slow. It is late :)


    Guy

  • Restrict Combobox Selection To List Only But Allow Deletion

    Re: Combobox Validation


    Quote from Dave Hawley
    Code
    Private Sub ComboBox1_Change()
    If ComboBox1 <> vbNullString And ComboBox1.ListIndex > 0 Then
    'CODE
    End If
End Sub


    Dave, thank you for your reply - as always you are a big help...


    I tried that code but it seems to give an error ONLY when the user picks from the ListIndex (choices in the combobox drop down). User can freeform anything and no error.


    Guy

  • Restrict Combobox Selection To List Only But Allow Deletion

    I have the following code which works but after the user gets an initial error says that they have to enter what is a ComboBox choice they get a second error when they press backspace to delete their entry. Is there a way to just give them the first error but not an error when they press backspace to erase their mistake in the form?


    This checks the user input...

    Code
    Private Sub Combobox5_Change()
    If ComboBox5.ListIndex < 0 Then
        MsgBox "Please Only Pick From The List.  Use Admin Page to Add More to the List", vbCritical, "Error"
    End If
End Sub


    This is what is loaded as ComboBox choices. I only want them to pick what is on this list. Nothing else.

    Code
    On Error Resume Next
    lngNRows = Range("Admin_Bikes").Rows.Count
    If lngNRows > 0 Then
        ComboBox5.RowSource = "Admin_Bikes"
    Else
        ComboBox5.RowSource = ""
        ComboBox5.AddItem "Empty"
    End If


    Thanks much,


    Guy

  • Find Date & Copy Row Insert Formulas Only

    Re: Find Date &amp; Copy Row Insert Formulas Only


    Quote from Derk

    Ooops. My apologies, Guy, I'm not sure what happened.
    Here is the code.
    [vba]Dim j As Long
    For j = 1 To Cells(1, 255).End(xlToLeft).Column
    If Cells(1, j).HasFormula Then Cells(1, j).Copy Cells(2, j)
    Next j[/vba]


    Thanks for your input. This is the code that I adapted and seems to work fine



    Derk, thank you for your help. Now on to filling in the values.

  • Find Date & Copy Row Insert Formulas Only

    Re: Find Date &amp; Copy Row Insert Formulas Only


    Quote from Derk

    The following copies any formulas in row 1 to row 2, ignoring values. Perhaps you can adapt it to your code.


    Derk, did you mean for there to be an attachment here? i don't see anything.


    Thanks,


    Guy