Search code examples
vbaexcelexcel-2007excel-2013userform

How to use UserForm Values for multiple things

I have UserForm1 which is a multipage userform and I am trying to access the information that was gathered through the form in a sub located in Module1. This sub will need to access several different values and do different things with those values so this is going to be a multipart question.

  1. I have the below code in which I attempt to use one of the values as the upper limit of a For Next Loop. However the current problem is that when the code reaches this line it jumps to the Userform_Initialize routine.
For X = 1 To UserForm1.LocalOffer.Value
  1. Second part of this question comes from inside the For Next loop from above. Where I have the below code. Which would ideally allow me to cycle through a series of similarly named Textboxes from the userform. Not even sure if that will work as the code keeps breaking before getting to that part.
Range("B" & X).Value = UserForm1.Controls("LocalTier" & Tier).Value
  1. Last Part of this question if I have a Textbox in the userform that contains a date in the format 1/18/2015 is there a way for me to grab just a portion of that date say for instance just the Day or just the last digit of the year?

I am using Excel 2013 but the file will be ran on Excel 2007

Edit: Turns out that problem 1 was fixed by not closing the userform with the X button but instead adding a line to hide the userform when you hit the last button. As it turns out my code for the second question worked just fine once i got past that. Only question left is the last one which I have no ideas on.

Solution

  • As from the comments, I see you don't need anymore to know about points 1 and 2, I will hence limit my answer to the point 3.

    Last Part of this question if I have a Textbox in the userform that contains a date in the format 1/18/2015 is there a way for me to grab just a portion of that date say for instance just the Day or just the last digit of the year?

    You can use either string manipulation, or date conversion. Let's assume the Textbox is called myDateTextbox

    String manipulation

    Among the string manipulators that VBA provides, I would cite Left() and Right().

    For example: 

    last_digit_of_the_year = Right(myDateTextbox.Text, 1)

    will return you the last character of the string. On the other hand: 

    first_digit = Left(myDateTextBox.Text,1)

    will return you the first digit of the string. You can use the Len(myDateTextBox.Text) built-in to return the current length of the string.

    Date conversion

    You can simply convert your string into date using the CDate() function. Please note this function will return an error if you pass an invalid string. If your textbox contains 24/01/1990, you can first convert the string into a date: 

    myDate = CDate(myDateTextBox.Text)

    Hence, you can retrieve day, month or year like this: 

    myYear = Year(myDate)
myMonth = Month(myDate)
myDay = Day(myDate)

    Please note that CDate recognizes date formats according to the locale setting of your system.. Hence, if the format in the TextBox is not the same than the one of your system, then consider manipulating the string before to adapt it to the proper format. For example, if your system has settings DD/MM/YYYY and your textbox shows a MM/DD/YYYY type, you can "adjust it" as follows: 

    wrongFormat = myDateTextBox.Text
splittedDate = Split(wrongFormat,"/")
goodFormat = splittedDate(1) & "/" & splittedDate(0) & "/" splittedDate(2)

    If wrongFormat was 1/18/2014 but your system would like to read it as 18/1/2014, it's now fine because goodFormat will be equal to 18/1/2014 after the split and re-build.