Posts by richadj4

  • macro to search though all worksheets

    Re: macro to search though all worksheets


    posting the actual code would've been helpful :(
    The problem appears to be the "else" code. It's completely unhelpful and simply replicates the then code. Remove (or comment out) this section entirely and it should work.

  • shift columns

    Re: shift columns


    really? wow. How big is your workbook???????
    try this

    Code
    Sub MoveStuff()
    ActiveSheet.Columns("A").Insert
    ActiveSheet.Columns("A").Value = ActiveSheet.Columns("H").Value
    ActiveSheet.Columns("H").Value = ""
    ActiveSheet.Columns("H").Delete
    ActiveSheet.Columns("B").Insert
    ActiveSheet.Columns("B").Value = ActiveSheet.Columns("G").Value
    ActiveSheet.Columns("G").Value = ""
    ActiveSheet.Columns("G").Delete
End Sub

  • Conditional formatting based on colour from multiple cells/worksheets

    Re: Conditional formatting based on colour from multiple cells/worksheets


    uh-huh, but "conditional formatting and/or forumlae cannot detect the color of a cell."
    therefore: "You will either need to use a UDF, a formula based on the formula used to determine to the color of the cells, or hidden cells (replicate the formula used to turn the original cell green in a hidden cell to set it to true or false)"

  • Conditional formatting based on colour from multiple cells/worksheets

    Re: Conditional formatting based on colour from multiple cells/worksheets


    conditional formatting and/or forumlae cannot detect the color of a cell.
    You will either need to use a UDF, a formula based on the formula used to determine to the color of the cells, or hidden cells (replicate the formula used to turn the original cell green in a hidden cell to set it to true or false)

  • shift columns

    Re: shift columns


    Code
    Sub MoveStuff()
    ActiveSheet.Columns("A").Insert
    ActiveSheet.Columns("A").Value = ActiveSheet.Columns("H").Value
    ActiveSheet.Columns("H").Delete
    ActiveSheet.Columns("B").Insert
    ActiveSheet.Columns("B").Value = ActiveSheet.Columns("G").Value
    ActiveSheet.Columns("G").Delete
End Sub


    It's a little rough, but this should work. Note that the columns H and G are used because you've inserted earlier columns, so G and F have "moved"

  • Loop code

    Re: Loop code


    Code
    Sub PopulateDates()
Dim wbReport As Workbook
    Set wbReport = Workbooks("Monthly Report.xlsx")
    i = 0
    For Each ws In ThisWorkbook.Sheets
        ws.Range("F4").Value = wbReport.Sheets("INFO").Range("G2").Offset(i, 0).Value
        i = i + 1
    Next
End Sub


    This assumes that 1) The monthly report workbook is Open, and 2) that the monthly report workbook is called "Monthly Report.xlsx"


    If the workbook name is wrong, just change it on the third line
    If the workbook is not open, then you will need code to open it (either a prompt to chose it, or if the path and name is standard, just an automatic)

  • Speed Up Code for Hiding and Showing Rows of Data

    Re: Speed Up Code for Hiding and Showing Rows of Data


    Not actually sure on this one. You've turned off screen updating, which is good, however I don't THINK a recalculation occurs when rows are hidden? however it will occur when setting calculation back to automatic, so try disabling the calculation lines, see if that makes a difference.

  • Multiple records on one UserForm

    Re: Multiple records on one UserForm


    The most recent one has several issues. In no particular order:
    1) Reducing boxnumber: The code I supplied should go directly above the boxnumber= line. This is after you've validated and identified that the new entry is in fact a valid number, and before you've started doing things with the number. To deal with the "first run" (so when you run it the first time and box number has not yet been set) in the Userform_Activate sub, set boxnumber to 0.


    2) Your existing code to set the values, sets a value in activecell, and then 3 times sets a value in the cell directly below it, so you are writing width into the cell, then OVERWRITING it with height, then again with weight.


    I think your approach here is lacking something. You can't (shouldn't) be looking for an empty column to write in (which is what the do until loop does), you should instead be looking for the column indicated by the current boxnumber (value in Me.txtBoxNumber.Value). Following your current code standards, this would look something like:

    Code
    Worksheets("Sheet1").Range("A2").offset(0,Me.txtBoxNumber.Value-1).value = LongV
  Worksheets("Sheet1").Range("A2").offset(1,Me.txtBoxNumber.Value-1).value = WidthV
  Worksheets("Sheet1").Range("A2").offset(2,Me.txtBoxNumber.Value-1).value = HeightV
  Worksheets("Sheet1").Range("A2").offset(3,Me.txtBoxNumber.Value-1).value = WeightV


    No loop is desired, because you are directly editing the correct column (identified by the current box), and making exactly 4 changes, one for each of your box variables.


    Regarding the "next box" Your approach is sound. You don't actually NEED a box variable (no harm if you want one). This statement "txt.BoxNumber should always be = to Box" If the txt.boxNumber should always be = to box (you're right, it should), then box is fairly useless. Anyplace you would use box, instead use txtBoxNumber.value


    Regarding "grab the first Box data and display it on the userform.":
    The way I would to this is to add code to the txtBoxNumber_Change event. This code should look for the appropriate column in your table (same sort of code that is used to save the values) and instead of WRITING the data TO the table, it should READ the data from the table, and put it in the appropriate edit boxes. When the value in the txtBoxNumber control indicates a new box, the column will be empty, so you will be reading blank values and putting these blank values into the text boxes. When it indicates an existing box (either because you've looped from the last box around to the first, or because you've "preved" instead of "nexted") it will read the existing values and placed them in the edit boxes.

  • Multiple records on one UserForm

    Re: Multiple records on one UserForm


    :) glad your making progress!
    1) this first issue "the data is filled into all boxes" is due to this loop:


    Code
    For i = 1 To BoxNumber
With Worksheets("Sheet1")
.Cells(2, i).Value = LongV
.Cells(3, i).Value = WidthV
.Cells(4, i).Value = HeightV
.Cells(5, i).Value = WeightV
End With
Next i


    The loop is not needed (because you don't want to loop over each box), simple set (1,Me.txtBoxNumber.Value).value etc


    2) The key here is that you already KNOW boxnumber. you've saved it as a variable. So BEFORE you save over it with the new boxnumber, you can do things with it. For example something like (untested):


    Code
    for i = me.cboBoxNumber.Value+1 to Boxnumber
   ThisWorkbook.Sheets("Sheet1").Range("A1").Offset(0, -1 + i).entirecolumn.value = ""
next


    should delete all entries in all cloumns from the NEW boxnumber +1 (5+1 in your example) to the old boxmaster (10 in your example), thus deleting all numvers in columns 6 to 10 when you change boxnumber from 10 to 5.
    (I hope that made sense)


    I believe (again, untested) your existing code should work fine when going from 10 to 15?


    A couple of additional tips/tricks from looking at your code.
    this :

    Code
    ThisWorkbook.Sheets("Sheet1").Range("A1").Offset(0, -1 + i).Select
ActiveCell.Value = "Box No. " & i


    is both unnecessary, and generally a "bad idea". you should (almost) never need to select anything. If you find yourself selecting a cell then doing something to the activecell, stop. It should be thusly:

    Code
    ThisWorkbook.Sheets("Sheet1").Range("A1").Offset(0, -1 + i).Value = "Box No. " & i

  • Multiple records on one UserForm

    Re: Multiple records on one UserForm


    Hi heytoluca,
    Not angry, just confirming, there IS (or at least WAS) a forum here where you can pay to have someone develop a solution. Just wanted to make sure you were in the right place.


    a Multipage is more...... intuitive, but it depends on how many boxes are likely/possible. a multipage with 20 tabs would be a BAD thing.


    looking at the code that you have currently, you have a ways to go.


    Firstly I'd look at how you are "storing" the information. You can of course store it in variables, but writing it to a worksheet is likely to be....... better. I'd look at your data structure, and determine what you need saved, and how to save it to start off with. The "Interface" is generally not the first you (I) do.


    With the userform, currently you save the boxnumber as a variable when the combobox is set, which is fine. personally, I'd disable the next and previous box, and only enable them when the user has input multiple boxes. so the on change event (or better the on exit event so that typing "10" doesn't trigger the code twice) of the combobox should 1)save the number of boxes, 2) enable the next button, 3) Set the box number to 1 4) move the focus to the long text box.


    When the Next button is pressed, check that all tesxtboxes have valid entries, SAVE everything related to that box somewhere (ie a sheet), increment box number, clear the text boxes, set the focus back to the long box.


    This should be enough to get you started. Slightly more advanced concepts include when the "box number" is changed (should only be automatic, but the event still fires), if the number is one, disable previous, otherwise enable it. if the number = the number of boxes, disable next, otherwise enable it.
    When next or previous is checked, (as above) validate the values in the text boxes and save the current "box", but THEN, increment or decrement the box number, and CHECK THE SHEET to see uif the user has already entered information for the new box number. If so display it, otherwise display blank fields


    I'm sure there is more, but that should be enough to get you started with your current approach.

  • Multiple records on one UserForm

    Re: Multiple records on one UserForm


    "Can you shed some light" is a little vague. where are you having difficulties?
    The information entered will need to be saved somewhere, either in a db or on a worksheet. Each of your textboxes has onEdit and onExit events.


    If you're looking for someone to do it for you, you're in the wrong place, if you want assistance you need to be more specific about where you're having difficulty and what you've tried

  • Deciphering between similar file names

    Re: Deciphering between similar file names


    1) The If statements are indeed in the wrong place, you're checking the string *AMEC* for photos, and of course it's not there, you need to be checking the File_Name variables
    2) IF I'm understanding the scenario accurately, you need a loop of some sort, because IF the dir command "finds" a filename with PHOTOS, it would ignore it (good), but it WOULDN'T go back and look for another one (BAD).


    I BELIEVE that repeated calls to dir should work......... from the help file
    "Dir returns the first file name that matches pathname. To get any additional file names that match pathname, call Dir again with no arguments. When no more file names match, Dir returns a zero-length string ("")."

  • Copy/Paste a Column Over Other Columns & Stopping Before a Specific Column

    Re: Copy/Paste a Column Over Other Columns & Stopping Before a Specific Column


    minor, but 4+5+6 <> 16 :(
    more relevantly, I don't think I get it.
    "Manually insert a column" This makes sense.
    copy/paste 5/2 over columns to the right: what? why?
    system in place to automatically.. : ummm, okay, is this "system" not working correctly?, what is it?


    I must be misunderstanding something, becuase it SOUNDS like you currently have to manually insert a column, and copy paste some data. You can of course automate this with VBA, but it's likely to "break" the "system" that does other things