Userform - VBA

[shina67]

Hi All,

I am currently trying to put bin locations to all the storage locations throughout the business
I need to identify all bins locations which if all typed in manually would take forever.

My thought process was a userform asking for the 1st location i.e. S001 and then the last location S200. These letter pre-fix changes to define the different areas of the business..
Then ask for the amount of sub locations within said locations i.e. A to F.

The macro then would produce 4 identical sized codes on an A4 sheet using the entire sheet with the following on them as an example.

*S001A*, *S001B*, *S001C* and so on until the criteria within the form has been met. As you can see all bin locations would start and end with an Asterix. This is for barcoding purposes.

The sizing of the font at this point and actual font are not a problem as I can alter that at a later stage when we have decided which barcode system we will be using.

I am hoping someone can help as I have said this would take an age to manually type out and my VBA is not good enough to be able to do this. I have tried previously doing userforms but always seem to struggle and get them wrong. Any help would be userful.

Thanks in advance.

 

[Micron]

I can probably work on this later and will suggest how it might be done. However, I figure someone will beat me to it.

The code would prompt for location (S), range (1 to 200) and sub station info (A to F). An outer loop would go from 1 to 200 and the inner loop from Asc([input as A]) To Asc([Input as F]) or put another way, from 65 to 70. It would concatenate "*S" & Format(range with leading zeros) & Chr(65) & "*" to give *S001A* within the first inner loop pass. Next pass, Chr(66) would return *S001B* and so on to F. After F, inner loop exits and outer loop repeats by using "2", so *S002. The inner loop must start over again at 65 to 70. As long as you're not throwing in any other requirements, that's how I see it. I'd suggest a userform for inputting all the variables although cell references could work also, but the form would provide a nice means of execution by way of a command button. Hope that helps anyone who can work on this before me, or they might even have a better idea.

 

[shina67]

I can probably work on this later and will suggest how it might be done. However, I figure someone will beat me to it.

The code would prompt for location (S), range (1 to 200) and sub station info (A to F). An outer loop would go from 1 to 200 and the inner loop from Asc([input as A]) To Asc([Input as F]) or put another way, from 65 to 70. It would concatenate "*S" & Format(range with leading zeros) & Chr(65) & "*" to give *S001A* within the first inner loop pass. Next pass, Chr(66) would return *S001B* and so on to F. After F, inner loop exits and outer loop repeats by using "2", so *S002. The inner loop must start over again at 65 to 70. As long as you're not throwing in any other requirements, that's how I see it. I'd suggest a userform for inputting all the variables although cell references could work also, but the form would provide a nice means of execution by way of a command button. Hope that helps anyone who can work on this before me, or they might even have a better idea.

Hi Micron,

Thanks for your reply.
My thought of a userform was so that the prefix "S" could be changed to anything. Also the 001-200 could be set differently i.e 6-152 or 106-634 as examples and then the same with the alphabetical on the end.
So that it all could be dynamic apart from the start and end characters which would be *.

Hope that makes sense. This is the reason I have struggled with it as it's so dynamic.

I appreciate your help.n

 

[Micron]

How about you create a form in a file and upload it somewhere e.g. dropbox, onedrive etc. then the code can match your control names?

 

[shina67]

How about you create a form in a file and upload it somewhere e.g. dropbox, onedrive etc. then the code can match your control names?

Hi Micron,

I don't understand what you mean

 

[Micron]

What part? Creating a userform? Uploading a file to a file share? Maybe Google either or both of those terms if you need help on how or what they mean.

 

[shina67]

How about you create a form in a file and upload it somewhere e.g. dropbox, onedrive etc. then the code can match your control names?

What sort of form in a file are you referring too?

 

[Micron]

Creating a userform? Here's 1 of 1.55 million hits

https://www.indeed.com/career-advice/career-development/excel-userform

I'm not getting a good feeling about your expertise in vba code or form design even though you mentioned you wanted a 'macro'.

 

[shina67]

Creating a userform? Here's 1 of 1.55 million hits

https://www.indeed.com/career-advice/career-development/excel-userform

I'm not getting a good feeling about your expertise in vba code or form design even though you mentioned you wanted a 'macro'.

I did mention in the 1st post "I have tried previously doing userforms but always seem to struggle and get them wrong."

 

[Micron]

After clarifying the requirements I created a userform and wrote code to get, validate and post the results to a sheet, and uploaded the file to a shared drive. The requirements included arranging the output in 2 columns. For the benefit of anyone who may be reviewing this thread in the future:

Validation and Calling Procedure

'you may want to make userform modal
Private Sub cmdRun_Click()
Dim ctl As Control
Dim i As Integer
Dim strPre As String, strSubStart As String, strSubEnd As String
Dim intArea1 As Integer, intArea2 As Integer

'loop over controls; check only textboxes for missing values
For Each ctl In Me.Controls
    If TypeName(ctl) = "TextBox" Then
        If ctl = "" Then
            MsgBox "All values are required"
            Exit Sub
        End If
    End If
Next

'upper case any lower case letters
strPre = UCase(Me.txtPrefix)
strSubStart = UCase(Me.txtSubStart)
strSubEnd = UCase(Me.txtSubEnd)
'ensure numbers are number data type and not strings
intArea1 = CInt(Me.txtAreaStart)
intArea2 = CInt(Me.txtAreaEnd)

Select Case True
    Case Not IsNumeric(Me.txtAreaStart) Or Not IsNumeric(Me.txtAreaEnd)
        MsgBox "Area start and end values must be numbers"
        Exit Sub
    Case IsNumeric(Me.txtSubStart) Or IsNumeric(Me.txtSubEnd)
        MsgBox "Sub Areas must be letters"
        Exit Sub
    Case intArea1 > intArea2
        MsgBox "Area start cannot be greater than area end"
        Exit Sub
    Case Asc(Me.txtSubStart) > Asc(Me.txtSubEnd)
        MsgBox "Sub area start cannot be greater than sub area end"
        Exit Sub
End Select

BuildString strPre, intArea1, intArea2, strSubStart, strSubEnd

End Sub

Called Procedure

'written as function in case the function caller needs a value
'otherwise, a sub  is sufficient to write to a sheet

Public Function BuildString(strPre As String, intArea1 As Integer, _
   intArea2 As Integer, strSub1 As String, strSub2 As String) As String
Dim i As Long, n As Long, Lrow As Long
Dim sht As Worksheet
Dim rng As Range

'assume prefix = S, low area = 1, high area = 5; low sub = A, high sub = D
Set sht = Sheets("Sheet1") 'change if required
For i = intArea1 To intArea2 'loop from 1 to 5
    For n = Asc(strSub1) To Asc(strSub2) 'loop from 65 to 70
        BuildString = "*" & strPre & Format(i, "000") & Chr(n) & "*"
        'get current last used row. If 0 this will cause 1st row to be empty
        Lrow = sht.Cells(Rows.Count, 1).End(xlUp).Row
        Set rng = sht.Range("A" & Lrow)
        'if on last row col A is not empty but col B is empty write to B
        If rng <> "" And rng.Offset(0, 1) = "" Then
            rng.Offset(0, 1) = BuildString
        Else 'write to A
            rng.Offset(1, 0) = BuildString
        End If
    Next
Next

End Function