Posts by Rocky13

    Re: Mouse Wheel scroll userform


    Nice that works.


    There is only a slight bug for some people, where they scroll around and then sometimes it just stops scrolling, this also happened with the old code. It would appear it only happens when you scroll up. This doesn't happen to me so I can only guess its a windows version issue or mouse type issue lol.


    I am still surprised this kind of stuff doesn't exist naturally in newer versions of excel.

    Hello,


    I have created a WebBrowser Object in a userform. Which is toggled on and of with the below code. The code creates a temp htm file to preview based on value found in a TextBox.


    The toggle works fine, however it breaks when I navigate away to another page.


    The user form is setup with a multipage that has two pages.


    When I come back to page 1 when I try to toggle the webbrowser I get the following error:


    ------
    Run-time Error '-2147467259 (80004005)'


    Method 'Navigate' of object 'IWebBrowser2' failed


    ---------



    It crashes when it reaches the following line: WebBrowser1.Navigate ("about:blank") in the code below



    Hi,


    I was wondering if its actually possible to make the userform scroll using the mouse wheel.


    I am able to find code by Peter Thornton that does this for listboxes and comboboxes by calling a windows api but am yet to find anything that works for the whole userform.


    If anyone knows a way I would greatly appreciate the help.

    Re: Codes for clearing userform textboxes


    ah thanks Roy I was looking to do something like this, by giving the user the option to reuse the data or clear the data, I did not want to have to close the form and reopen though, or clear every single value one by one lol.

    Re: Update cells in a range with drop list based on values in another range


    Here it is,


    I stripped out the part where it downloads the data and trimmed it down as the lfile was too big and left the loaded data in Sheet1. I also added a DESIREDOUTPUT tab so you can see what the final result should be.




    ozgrid.com/forum/core/index.php?attachment/56321/

    Hi,


    I am having some small issue with updating cells based on values in a range to a drop list.



    1. First thing I am doing is populating data on Sheet1. This is done by a macro button on Sheet1.
    2. On Sheet2 I check if the data is populated and return TRUE into cell A1.
    3. If A1 is true, I want to check the range of C10:C1000. If any of these cells contain the word "FIXED" then I want to populate the same cell row in another range D10:1001 with a drop list menu called new_fixed_data.




    Current screen:
    [TABLE="width: 500"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    FALSE

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td][/td]


    [td][/td]


    [td]

    FIXED

    [/td]


    [td]

    Update Data Table

    [/td]


    [/tr]


    [tr]


    [td]

    11

    [/td]


    [td][/td]


    [td][/td]


    [td]

    VARIABLE

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    12

    [/td]


    [td][/td]


    [td][/td]


    [td]

    FIXED

    [/td]


    [td]

    Update Data Table

    [/td]


    [/tr]


    [tr]


    [td]

    13

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    14

    [/td]


    [td][/td]


    [td][/td]


    [td]

    FIXED

    [/td]


    [td]

    Update Data Table

    [/td]


    [/tr]


    [/TABLE]


    Desired output once TRUE:
    [TABLE="width: 500"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    TRUE

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td][/td]


    [td][/td]


    [td]

    FIXED

    [/td]


    [td]

    DROP LIST

    [/td]


    [/tr]


    [tr]


    [td]

    11

    [/td]


    [td][/td]


    [td][/td]


    [td]

    VARIABLE

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    12

    [/td]


    [td][/td]


    [td][/td]


    [td]

    FIXED

    [/td]


    [td]

    DROP LIST

    [/td]


    [/tr]


    [tr]


    [td]

    13

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    14

    [/td]


    [td][/td]


    [td][/td]


    [td]

    FIXED

    [/td]


    [td]

    DROP LIST

    [/td]


    [/tr]


    [/TABLE]



    code i am using:




    Thank you for look.

    Hello,


    I was hoping this would be a simply problem but I could not find the answer.


    What I am doing is when I double click on a cell a userform opens with two options (remote image and local image). When I click on local image a FIilePen Dialog opens and I can select the image to open. The result returns the file path to the image file name. Instead I would liek to return the binary for that image into the cell. Is that possible?


    The reason I need the binary value is because it is a bulk upload csv file which pumps data into a database via php. The binary of that image could then be uploaded to recreate the image. Alternatively, is there a better way to upload images via a bulk csv from local machine?



    Code use to return file path and file name
    i.e. c:\temp\image.jpg


    Re: Runtime Error 1004 Unable To Set the Locked Property of the Range Class


    Quote from rory;665215

    Assuming that code is in the worksheet module, you should use Me rather than Activesheet because your sheet may not be the active one when it is changed. Also, I assume you are disabling events somewhere in the full code given that you are changing values from a Change event.


    Hey,


    Thanks for the feedback and help. I think I got it fixed, so far no dummy spits. I didn't have any events disabled as I wasn't aware of this feature lol. I added it to my code and presto seems to be working.


    PS: I'm sure by the sloppy code you see above you can tell i'm a novice who picks up tricks here and there on the forums haha.

    I have searched the boards but I could not find the answer. The only thing it pointed to was merged cells however I do not have any. If someone wants to look at the file, I can only email as its too big to upload.


    I get this error when trying to delete data or add data in particular columns.


    Effectively what I am doing is Private Sub Worksheet_Change(ByVal Target As Range) to find changes made to certain columns, then executing something if true.


    The columns that are causing me the issue are K, BC, BD, BE. I suspect it is the way I protect and unprotect the worksheet.



    Step 1 - If a certain value is selected from a drop listing in column K (If Target.Column = 11 Then), then populate drop lists in AZ, BA, BB, BC, BE, or populate N/A in these fields if the selected value in K requires no further information.


    Step 2 - Then user can select data from drop list in Column BC has a drop list. Depending on the value they select in column BC (using If Target.Column = 55 Then), column BD is populated with specific drop lists for that NAME.


    Step 3 - Depending on the value entered in column BD (using If Target.Column = 56 Then), column BE is either populated with a drop list or hard value of N/A.






    Sample CODE of what happens in step 1

    Re: How to validate data in one range based on another range


    Quote from maudibe;663442

    Rocky,


    Just copied the routine you modified over into a module and ran it. On the money Bro!! Just keep the hearder row and you are there. Great job!


    Maud


    What do you mean by header row? Do you mean include "There are values missing in:"


    Also, thanks heaps for your help, your code works great for a small number of column validations, I didn't consider that it would get very complex the more column names I wanted to return, hence why i kept the example short and sweet. So apologies for making you write that code, but I am sure it will come in handy for future very simple applications.

    Re: How to validate data in one range based on another range


    Something like:



    Re: How to validate data in one range based on another range


    maudibe,



    Thank you for your help, that works a treat.


    Quick question, if my spread sheet needs more columns validated in the future potentially up to 15 (not sure at this stage) or so out of 35.


    All I would need to do is just put more embedded IF statements till I get to the desired level? Also I would need to increase the missing array from (1,3) to (1,n), n for number of total columns needing validation minus one for the Row number. Then for message add all the relevant missing array objects into a string correct? however this can get very long with so many possible combinations.



    Due to the complexities that this may cause, rather than returning each column name that is missing and all the possible combinations of missing columns, in your view, would it be easier to just do a hybrid of your original code and your new code to find where there is a missing value along that row no matter if 1 missing or 15 missing (to create the one message box of multiple rows) that says something along the lines of:


    -----------
    Row 1 is missing data.
    Row 2 is missing data.
    etc..
    etc..
    Please check that the following columns (test, output, timezone, date, colour, etc...) have data entered on each of these rows.
    ------------

    Re: How to validate data in one range based on another range


    Hey, thank you this is almost it.


    What I need it to return is all that in one message box, rather than multiple msgboxes with each row that is missing data.


    Also I would like to get the heading of the column it is missing data in.


    Assume column C for example is called TEST and column D is called OUTPUT. The heading row may not necessarily be in A1:D1 as I may put a macro button on top of sheet and some other summary variables, so may be A5:D5 for example. I really dont know yet as I am planning it out.



    So ideal output in one msgbox would be:


    There are values missing in row 1, column Test.
    There are values missing in row 2, column Test, column OUTPUT


    or


    There are values missing in:
    Row 1, column Test.
    Row 2, column Test, column OUTPUT





    I have a macro that print a certain sheet "result". However I want to validate if certain columns are blank before printing.


    I haven't created this yet but what I need is essentially to validate that certain cells are not blank only when data is present in the range a2:a. I would like a message box to show up if certain (not all) columns cells are null


    E.g.


    [TABLE="width: 500"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B+

    [/td]


    [td]

    C*

    [/td]


    [td]

    D*

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Tom

    [/td]


    [td][/td]


    [td]

    YES

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    Jim

    [/td]


    [td]

    YES

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    YES

    [/td]


    [/tr]


    [/TABLE]


    + doesn't need validation
    * needs validation


    When clicking on print button:



    Output in msg box
    ------
    Row 1 is missing data in column_name (D) (i.e. cell D1)
    Row 2 is missing data in column_name (C) and column_name (D) (i.e. cells C2 and D2)
    ------



    Row 3 isn't validated as A3 is empty

    Re: Exporting to CSV ignore blank rows with hidden formulas


    Quote from jindon;661316

    No I can't open it.


    Thank you for your help.


    I think I solved the issue.


    I decided to test your updated macro in a new fresh spreadsheet and it worked perfectly.


    So in my spreadsheet I went back to the HIDDEN sheet.


    In the HIDDEN sheet what I did was after the final column and rows, hide all other columns (AFTER AO) that I did not need and same for row (row 1000).


    Then I ran the macro and it works fine, it does not add any garbage on the end of the file.


    So I tried hiding the stuff again and sure enough the garbage returned.



    SOLUTION FOR OTHERS WHO COME ACROSS THIS:
    Don't hide rows and columns at end of data and it will work fine.

    Re: Exporting to CSV ignore blank rows with hidden formulas


    Quote from jindon;661312

    Then I really need to see your actual file (Excel)


    I attached it to the original post in two small zips.


    the second file need to remove the .zip and let it be just .z01 (had to change as the site would not let me upload .z01 part file)


    I also tried to force set all the values in the new spreadsheet to nulls, but same final result (using office 2010 if that makes any difference).


    Code
    1. With .Sheets(1).UsedRange
    2. .Value = vbNullString
    3. End With