Posts by rdkalinowsky

    Re: Capturing Data From Userforms Created At Run Time

    I'm not permitted to upload code from my office, but it looks similar to the code from Andy Pope's attachment on Page 1 in the aforementioned thread, but I modified it such that the number of label and textbox rows are passed via another process instead of read from a spin control. Can we use his code for sake of discussion?

    Re: Capturing Data From Userforms Created At Run Time

    That may work, but this concept is a bit over my head...any chance you might be able to provide a piece of code that demonstrates the task? I'm struggling with how to structure the block of code...are you saying to somehow define each control during the creation of the form, then read the array values from the controls independently? I'm a bit lost...:(


    I found a useful thread that was very helpful in showing me how to create dynamically scalable UserForms at run time.

    I'm now stuck on the obvious Part 2 that isn't included...How does one capture the values in the text fields without knowing the names of those fields.

    My initial thought is to use a For loop to read the values into an array, like so...

    Dim ReadArray (TextFieldCount)
    For k = 1 To TextFieldCount
         ReadArray(k) = ?????????
    Next k

    If it were a simple read from a text field I'd use something like:
    Set ReadField = TextBox1.Value

    Can anyone help me figure out how these autogenerated fields will be named? Thanks.

    Hi all,

    I'm in need of an easy VBA way to determine if a worksheet is a chart sheet. I'm pulling data from multiple closed workbooks and then performing several operations (mostly numerical averaging) on the imported data. Each source workbook contains a mix of dozens of standard worksheets and charts. I'd like my code to import data from only the grid sheets and ignore the charts, as runtime errors would be generated.

    The basic starting flow for each workbook is:

    When a chart sheet is found in the source workbook, the "SheetName = ..." line generates a runtime error - I've deduced that it's because it's a chart sheet, as the code handles all grid sheets without incident.

    I need to add an IF statement to test for a chart sheet. Any help? Thanks.

    OK, here's a unique one.

    I'm trying to automate the importing and processing of a fairly large formatted text file (~15000 lines) containing many tables of data. I've been refining this process for several months and recently was alerted to a problem. A few of my tables have widths greater than 255 characters (309 to be exact).

    My process involves opening the text file in Excel then performing a .Copy on the Worksheet into ThisWorkbook, naming the sheet "Source". I then perform up to 66 separate parse operations on the various tables depending on which options a user selected from a form.

    The problem is that I assumed that the Copy Method would simply make a duplicate of that sheet in ThisWorkbook. Instead, it seems to have truncated any columns that are longer than 255 characters, leaving me with partial data in a few tables.

    My workaround was to perform a TextToColumns on the text file prior to the import such that it would simply split the data into columns that could be reassembled on the other side. In doing so, I seemed to have stripped a leading <space> from each cell which was apparently put there previously. This is causing a lot of problems for my formatting code which also uses TextToColumns to separate the tables into columns. The simplest solution seems to be trying to add the spaces back in, assuming there isn't a better way to import this data in the first place.

    Just prior to copying the sheet into ThisWorkbook, I'm using the following code after the TextToColumns to add the space back in, but it's taking an awfully long time and causing a noticable time delay in processing my code and adding considerable size to the finished workbook it creates. If someone could recommend a better solution, either to the import process or to replace this God-awful loop, I'd appreciate it. Thanks.

    Application.ScreenUpdating = False
    For i = 1 To 65535
       wkbk.Sheets(1).Cells(i,3).Formula = " " & wkbk.Sheets(1).Cells(i,1).Value
       wkbk.Sheets(1).Cells(i,4).Formula = " " & wkbk.Sheets(1).Cells(i,2).Value
       wkbk.Sheets(1).Cells(i,1).Formula = wkbk.Sheets(1).Cells(i,3).Value
       wkbk.Sheets(1).Cells(i,2).Formula = wkbk.Sheets(1).Cells(i,4).Value
    Next i
    Application.ScreenUpdating = True

    Also, I don't know how long the actual file will be, so I'm almost forced to assume 65535...

    Re: Formula Entry Into Chart Title

    In this case I do need a macro, as this task is part of a large automation process where I'm creating dozens of worksheets and charts. I've been struggling with this chart titling for a few days now. It's frustratingly simple in the GUI but I can't get it to work in VBA...That said, you may have something there by creating the string in a cell first, then referencing it in the chart title...hmmm.

    Hi all,

    I've found several threads describing how to make a chart title dynamic by entering a cell reference into the formula bar with the chart title selected. I am wondering if anyone knows if it is possible to have VBA "code" a formula into a chart title that would be a concatenation of several strings.

    Ideally, I'd like to specify part of the title from a variable, and another part from a user-specified range, as in the following snippet from a module of mine:

    ChartSubtitleText = Sheets("Sheet1").Range("A5")
    Selection.Characters.Text = ChartTitleText & Chr(10) & ChartSubtitleText

    where ChartTitleText is a string variable that is passed to the Sub.

    This works fine and correctly builds the title block during execution of the macro, but I'd like to modify the capability to allow the user to enter a new value in Sheet1!A5 and have all of the charts update dynamically without re-executing the macro.

    Is there a way to have VBA create the chart and build the Chart Title with a formula that will allow for dynamic updating? I've tried having it enter the following line into the formula bar, but get errors:

    =CONCATENATE(ChartTitleText, CHAR(10), Sheet1!A5)

    Any suggestions would be very helpful. Sorry, but I can't post more of my code for security reasons.

    Hi all.

    I've found some threads that describe how to build a hyperlinked list of worksheets as a Sheet Index.

    My project has several "charts on new sheets" and I'd like to add them to the index as well in a separate column, B:B for instance.

    Here is my code. It gives a bad reference, seemingly since there is no visible attach point for the HTML code. Any ideas? Thanks.


    Re: Inserting Several Images Into Separate New Slides

    A colleague of mine needs to use something identical to this algorithm, but is using PowerPoint 2000 SP3 and we're getting errors, likely do to version compatibility. Could someone suggest a version of Andy Pope's code above that will work in 2000? Thanks in advance.

    Re: Combine Multiple Rows Into One Row

    Figured it out. Whew...that only took all morning and about 150 test runs. For the benefit of future searchers, here's the code.

    Re: Add Hours To Date And Time

    Excel also will allow you to add numeric decimal days to any time field. To add one day, simply refer to the cell and add "1" to it. For the record, 47 hours is 1.958333 days, so try =A1+1.958333.