Posts by Bagpussot

Re: Finding, Searching and next available Date return to UF TextBox

Hi skywriter,

Having taken a look look at the amendments we are definitely moving in the right direction. What you have coded works so far well thank you.

What I did notice during testing, is that the date search only seems to look at the last iPad selection entry in the list of return dates, e.g. If the chosen iPad is booked earlier in the sheet - refer row 2 retuning 28 October, the coding won't pick this up suggesting the lookup date function is searching for the last entry - or bottom up (?) which you made reference to earlier in our thread.
I would like the code to find the latest date in the return (booking in) list, so if I enter today's date for booking out, the code I cannot as the selected iPad is booked out until 28 October.

The other item I noticed, if there is a few days in between dates, the code only allows the next available date based on latest booking in (return) date. e.g. iPad 1 booked out 10 Oct to 12 Oct, then another booking for the same iPad 15 Oct to 19 Oct, then the same iPad cannot be booked out between 13 and 14 Oct.

is is this something that can be adjusted or is this not possible?

I belive this is your question earlier looking from bottom up for last entry. I would like to search all booking in (return) dates column for the specific User selected iPad so that the latest date (or longest date) in the list dictates the next available booking out date.

If you look at the your provided update example re-attached, you will see those entries I have added, where I can enter dates in October for iPad 1, yet in row 2 it has already been booked out until 28 October. I hope this makes sense?

if we can tweak this bit, we will be over the main hurdle, as I think the other bits will fall into place.
In parallel, I am adjusting the UF to control the user more appropriately when entering date to force the check in.ine with your current adjustments so will share when completed.

look forward to your feedback, thank you

Re: Finding, Searching and next available Date return to UF TextBox

Thank you skywriter

let me take a look and review, will come back to you in a day or two.

really appreciate your assistance

Re: Finding, Searching and next available Date return to UF TextBox

Hi skywriter

Thanks for coming back, appreciate my anticipated requirements of the UF maybe a little complex in explanation so will try to list simple process steps (in my layman terms!)

Scenario
User selects iPad from one of those shown in the UF ( currently 1, 2 or 3 for development of code but will be more)
User enters date for booking out the selected iPad
The VBA checks the dates previously logged within the worksheet for the User selected iPad to check availability of that same iPad against the latest booking in (return) date.

e.g. If the iPad selected is prior booked out and due to be returned say 14 October 2016, then the same iPad will only be available from 15 October for the next booking.

If the selected iPad is not available for the User selected date, a msgbox states which next iPad is available (based on the next nearest available date in the worksheet) for User selected date. If User accepts this new iPad and date, the date TB is cleared and populated with the new available date generated automatically.

Date TB now shows new booking out date and User sees the available iPad radiobutton now selected
User then enters booking in (return) date.
If User submits the UF, UF values then populate the next available empty row (this bit already works)

Senario Alternative
User selects iPad for booking out
User enters date in TB to book out
codes checks dates for same iPad against those already in worksheet
If User selected booking out date is a available Msgbox confirms iPad is available and User enter Booking in (return) date
on submitting UF, UF data is entered into next available row in worksheet.

if User selected date is not available, Msgbox states "iPad unavailable for chosen date, please change date or iPad selection."
UF is cleared ready for User to resellect a new date and or iPad,
code checks dates for newly selected iPad and date for booking out
if not available Msgbox states "IPad unavailable for chosen date..." Etc and clear UF to repeat process,
othwerwise, User enter Booking in (return) date and submit to book.
UF values are entered in next available row in worksheet.

Either way would work well for me for this system to work. The object is to avoid date clashes between the User requested booking out date and the latest return date (using current date as datum) which will result in no iPads being double booked due to date clashes.

I belive only the booking out date needs checking within the code against the booking in(return) dates located on the worksheet.

I am not set in stone with this process, so happy if you think an easier process can be coded. ( Two minds are greater than one!)

Hopefully this will assist your understanding further,

Many thanks in advance,

Re: Finding, Searching and next available Date return to UF TextBox

Hi Skywriter et al,

Have you you been able to identify a solution to assist with my problem?

Happy to redesign if you can suggest an alternative method to achieve what I wish to do?

I have looked through other aspects regarding alternatives allowing a date picker, streamlining the whole process, however, have also found issues where a date picker is not always available accross different MS Excel versions without installing an add on, so do not think this will be an alternative solution either.

Look forward to receiving your feedback,

Re: Finding, Searching and next available Date return to UF TextBox

Quote from skywriter;778274

So with your workbook let me ask you some questions:

1) When it comes to a particular iPad we are only interested in the entries farthest down in the table because the higher ones are historical?

2) Is the date in blank until it comes back or is that entered when the iPad is loaned out, in other words are we judging whether an iPad is available or not by an entry with a blank date in or are we comparing the date in with today's date to figure that out?

Hi skywriter,

I hope this will assist your understanding of my needs....

A1) - No - I am seeking which device (1, 2 or 3) is available for booking out based on the current return dates already within the worksheet. If a device is due to be retuned October 20, then its next available book out date is October 21 - this would be the date to return in textbox1 after user selected device option button 1, 2 or 3.

I would like to check in parallel the other devices.

• If User selects device 1, then enters book out date in textbox1, code searches existing device list (Col B) and the respective next available date.
• If User device selected (option button) or the User date entered is not available, an alternative device (e.g. device 2 or 3) maybe provide for the User date entered.
• If both are "False" (not available), then the next available device and next available date is provided to User to the userform.

A2) - The date in (textbox2) is completed by User as an estimated return date. Upon clicking submit, the information writes to the worksheet record.

• The date in (and other row entries) on worksheet is blank until User submits the completed UserForm (device, date out, date in, Name, Discipline, Project)
• Date in should be checked against current date and the "date in" entries already in Col E to establish next available date for "booking out date" in textbox1.

I maybe "designing" the process to be too complicated, other considerations may be available I am not aware of, so open to alternatives for this "simple" form !

Thanks in advance

Re: Finding, Searching and next available Date return to UF TextBox

I can certainly understand the workbook request skywriter and thanks for suggesting.

Attached as directed, and many thanks for the guidance for uploading, great help.

I have several modules in the workbook where I am experimenting with different types of dates codes find functions, once I have something together, surplus get deleted....its a learning curve for me !

Appreciate the guidance from the gurus here and of course suggestions if I can make the whole process easier....two minds (or more) are always better than a blinkered one!

I'm keen to learn, but baby steps please....

Hi,

I have a userform setup simply to allow users to book out equipment from a option button selection.
Each option button has the device serial number.
The user use to enter a book out date and a book in date - problem - dates would clash!

I have update the form, and can find the next available date to return to Textbox1, requiring the user to place a return date. Upon completion of the user name, and two other reference all in textboxes, the command button (submit) writes the date to sheet1. Great!

The key data populate in B2 onwards (unique device name), with D2 being booking out date, E2 being the return date. As each user enters a selection and dates, these populate from Userform to sheet 1.

The problem, I am unable to set up a search/find function to take user device selection on Userform (e.g Device1, Device2, Device3) and lookup the next available device date.
My code finds latest date, but cannot see how to add in the find function to check user selection and next date together.

i.e. User wants Device 1 for 7 Oct to 10 Oct, however Device 1 is already booked out 6 Oct to 8 Oct, therefore should populate UF Textbox1 with 9 Oct being the next date available. I have 5 devices currently to record.

In this circumstance, I would like to return which device is available also, perhaps Device 3 is available, but user only select Device1

Searching codes, i have added the date element, but cannot figure out the find / search part. Code so far, which returns the latest date in TextBox1:

Sub DeliveryDate()




    Dim col As Integer
    Dim device As Integer
    Dim lastrow As Long, i As Long
    Dim myDate As Variant
    Dim myDevice As Variant




    col = 5 
    
    device = 2
    
    Range(B).Find = Cells(Rows.Count, device).End(xlUp).Row




    lastrow = Cells(Rows.Count, col).End(xlUp).Row 'FIND LASTROW OF COLUMN STATED ABOVE




    myDate = CDate(Application.WorksheetFunction.Max(Columns(col))) 'TAKE THE LATEST DATE
    
    'myDevice = SearchColumnB:=UserForm1.ObtionButton1.Text




    'GO THROUGH EVERY ROW FROM 1 TO LAST AND FILL WITH DATE
    
    For i = 2 To lastrow
        UserForm1.TextBox1.Text = myDate
        'Cells(i, col) = myDate
        
    Next








End Sub

Guidance to how to best solve this appreciated, there maybe an easier way to set up?

As always, thank you in advance

Re: Skip Formula Columns in Sheet when Userform active for editing entries

cytop,

May I extend my sincerest thanks - I was not looking at placing an if statement for locked textboxes in the module itself which is why I was having issues - simple and effective - now I see it I understand your earlier reference and voila - my formulas are left intact on the worksheet.

I added your suggestion into both the get data and the edit return function of the module with total success, having crossed checked again my required textbox properties being locked = False for sanity sake.

Thank you so much for your time - works like a dream! :cheers:

Re: Skip Formula Columns in Sheet when Userform active for editing entries

Quote from cytop;778005

Using a phone, so comments only, and I may not have understood correctly but you could try setting the Locked property of those textboxes containing formula results to True. That'll prevent the user changing those .

Then, when writing back to the worksheet check if a textbox is locked before writing its value to the worksheet.

Thanks Cytop and for your timely response - appreciated.

I have tried (I believe correctly) in locking the textbox in object properties without success and within the Userform code under each textbox trying variation under textbox12_change event, textbox12_beforeupdate and textbox12_afterupdate events - all without success so far, using a simple line "TextBox12.Locked = True"

The formula resides within the workbook, that the userform "gets data" from and not within the Userform Textbox itself. My userform pulls data (values or strings) from the workbook to allow the User to make changes to a specific selected row ID# and then return any modifications back to the worksheet, in it simplest form.

Your suggestion and other things I have tried, continually replaces the worksheet cell containing the formula. Because the Userform has collected the value of that cell (the formula result string) into the Userform via the module code. Hence, when writing back to the worksheet, the Userform has only the textbox value obtained, thus overwriting the worksheet cell formula with the textbox value from the earlier "get data" module (as you would expect).

What I need is to somehow skip these cells containing the formulas, so the userform has nothing to write back into the worksheet leaving the formula intact and operational. I need all text boxes on userform as the module looks up all predetermined columns (2-38) to get the row data. If I remove these columns, my module of course will find error at the first column not being available to populate a textbox. E.g Col 0 =Textbox_1, Col 1 = Textbox_2 Col 2 = Textbox_3 etc.

I cannot currently identify a way to stop this "overwrite", to leave the worksheet cell formula intact.

Thanks for any additional suggestions,

Hi,

I have a workbook with 116 Columns in Excel 2013. The first 38 Cols have strings or dates which the User can initialise a Userform to edit the existing strings or dates or can add new row of data to the sheet.
My problem, I have several columns with formula to calculate days between date entered and current date (today) and a countdown of days alongside in the next column. E.g. Col K is User input, Col L and M have formula pre-entered in the sheet.

My userform loop code (thanks to OzGrid) allows the User to obtain the entry to edit by selecting a unique row ID#. Works perfectly replacing / overwriting the current cell contents as required, but I need to "skip" the cells/columns containing formula when the User writes back to the sheet to leave the formula cells "untouched" by the module. There are 20 columns with the formula contained in each row cell of the respective column. The are not numerically sequential.
E.g Col K User Data, Col L Formula, Col M, then Col N User Data, Col O Formula, Col P Formula, Col Q User Data R Formula, Col S Formula and so on

I have tried including the formula with the VBA Userform code, without success, as the module uses uses textbox reference to get the date and replace base on Userform content (unless cancelled !)
I have tried hidden textboxes on the Userform to collect formula and cancel any change event for the respective textbox to cell value without success.

So Questions:

1. Is there a way with my self-taught/learning VBA "skills", to have VBA skip these columns within the loop control, or
2. Is there a way to exclude through a change event and leave the formula intact or
3. Can the loop be adjusted to skip or not overwrite the formulas (perhaps by using a range reference?)

Open to other suggestions in how to achieve this of course, being mindful my limited VBA knowledge - no doubt making life too complex and there is a sensible and easier solution available

Have placed relevant get and edit module code below for reference:

The first part looks up ID# User place sin Textbox 1 and populates Userform

Dim id As Integer, i As Integer, j As Integer, flag As Boolean




Sub GetData()




If IsNumeric(DataEditUserForm.TextBox1.Value) Then
    flag = False
    i = 0
    id = DataEditUserForm.TextBox1.Value




    Do While Cells(i + 5, 1).Value <> ""




        If Cells(i + 5, 1).Value = id Then
            flag = True
            For j = 2 To 38
                DataEditUserForm.Controls("TextBox" & j).Value = Cells(i + 5, j).Value
            Next j
        End If




        i = i + 1




    Loop




    If flag = False Then
        For j = 2 To 38
            DataEditUserForm.Controls("TextBox" & j).Value = ""
        Next j
    End If




Else
    ClearForm
End If




End Sub

This part allows any editing / changes to be placed back in workbook (sheet 1) - including my formulas !

Sub EditAdd()




'Note to myself - Text boxes must be in sequential order




ActiveSheet.Unprotect "password removed for this post"




Dim emptyRow As Long




If DataEditUserForm.TextBox1.Value <> "" Then
    flag = False
    i = 0
    id = DataEditUserForm.TextBox1.Value
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1




    Do While Cells(i + 5, 1).Value <> ""




        If Cells(i + 5, 1).Value = id Then
            flag = True
            For j = 2 To 38
                Cells(i + 5, j).Value = DataEditUserForm.Controls("TextBox" & j).Value
            Next j
        End If




        i = i + 1




    Loop




    If flag = False Then
        For j = 2 To 38
            Cells(emptyRow, j).Value = DataEditUserForm.Controls("TextBox" & j).Value
        Next j
    End If




End If




ActiveSheet.Protect "password removed for this post", True, True




End Sub

Screen shot of workbook area of issues is hopefully attached also.

[ATTACH=CONFIG]70282[/ATTACH]

My extended thanks in advance,

Re: Userforms - multi listbox user selections - cannot force new line in same cell

You've been a great help and I appreciate your time in helping me to conclude my problem successfully.

The explanation and coding above I will take a more detailed look at and understand how it works.

Once again many thanks.

Re: Userforms - multi listbox user selections - cannot force new line in same cell

Never too simplistic - thank you so much Cytop, works like a dream and tested several times now ! Goes to show how much I still need to learn !

The basis of the below was from another moderator on Ozgrid, so thanks to Krishnakumar also.

Final Code to help others learning like myself perhaps?

Dim i   As Long, txt As String, Flg As Boolean
 
With Me.ListBox1
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            Flg = True
            'txt = txt & "," & .List(i)
            txt = txt & Chr(10) & .List(i)
        End If
    Next
End With
If Flg Then
    With Sheets("Sheet1") 'change to your own sheet name
        .Range("H" & .Rows.Count).End(xlUp).Offset(1).Value = Mid$(txt, 2) ' change "H" to required Column
    End With
End If

Would you be kind enough to briefly explain why the '& "," & does not work yet the & Chr(10) & does ?

I was led to believe placing a character between quotes ( or text) would allow VBA to pickup the quoted element, being an actual "item" within the userform, similar to specifying the actual sheet name as opposed to "Sheet1" for example. All the split function I came across use the " find the delimiter".

Also, how to I uprate / score you please to say a big thank you on the forum ?

Hi, this is my first post and I hope I have followed the rules as directed correctly.

I have searched many forums for developing my VBA Userform, and as a beginner, I have created my userform through test samples and other peoples guidance/code as I came across error/problems.
As a result,pieced bits together I have now created something which works, has some dynamic coding, with drop down choices interactive sheet pre-selections from prior user selections navigating the user automatically to the next required page based on selection (all pages are hidden to the user upon initialise).

• My userform is multipage, populates listbox and combobox via "Additem" using a variety of cases/index within the VBA coding. Nothing is populated from the excel sheet.
• The userform is actioned via a click event on the sheet to action the UserForm_Initialize() sub for user entries.
• The populated excel sheet "Sheet1" for example, works as I want, populating the sheet from framed option buttons, text boxes, dates and auto unique ID for each new row created by userform as each user enters data, all good and very pleased.

Column H, is where the ListBox1 populates the user selected choices. The Listbox1 is populated via two ComboBoxes. The First ComboBox selection dictates the contents of the second ComboBox. The second ComboBox user selection, dictates the ListBox1 contents for the User to select one or more items. (fmMultiSelectMulti)

• As an example, next available clear row is populated with the userform details, so if row 2 is already filled with data, row 3 will be next, with each column receiving specific userform data i.e Col A = ID#, Col B = Name, Col C = Project Number etc. Col H received the ListBox1 user selected strings. these populate the next available cell in Col H with a comma separated string(s). these range from a single predefined selection choice upto 10 choices.

In Col H, (assuming the user selects four of the choices available from the list), the cell contents after click control (OK) this looks like:

Text String Selected No 1,Text String Selected No 2,Text String Selected No 3,Text String Selected No 4,etc

I want to have:

Text String Selected No 1
Text String Selected No 2
Text String Selected No 3
Text String Selected No 4
etc

I have tried using the split function without success, which is no doubt my not understanding all the coding language, so my question is how can I achieve a forced line in the cell for each user selection chosen when "printing" to the sheet?

I have pasted the excerpt of code below which I am having difficulty.

Private Sub OKButton_Click()




Dim emptyRow As Long




'Make Sheet1 active
Sheet1.Activate




ActiveSheet.Unprotect "password"




'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1








'Transfer information
Cells(emptyRow, 1).Value = IDTextBox.Value
Cells(emptyRow, 3).Value = ProjectNumberTextBox.Value
Cells(emptyRow, 4).Value = CompletedByTextBox.Value
Cells(emptyRow, 5).Value = DateTextBox.Value


'Transfers selected items to Col H
'The Flg is a TRUE/FALSE variable which becomes TRUE when there is one or more item selected in Listbox.




Dim i   As Long, txt As String, Flg As Boolean
 
With Me.ListBox1
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            Flg = True
            txt = txt & "," & .List(i)
        End If
    Next
End With
If Flg Then
    With Sheets("Design Checklist and QAQC")
        .Range("H" & .Rows.Count).End(xlUp).Offset(1).Value = Mid$(txt, 2)
    End With


'the above code works fine and populates into Col H next available row and correct cell, but is not split into forced lines


'new code (2) to split text in active cell - trying to integrate it with above working code


Dim strInput As String, strOutput As String
Dim varZz As Variant, j As Integer ' change from i to j as error with duplicate i
     
    strInput = Active.Cell.Value ' tried changing from activecell.Value to Flg to txt none worked.
    varZz = Split(strInput, ",") 'thought this would help split my txt
    For j = LBound(varZz) To UBound(varZz)
        strOutput = varZz(j)
    Next j


'end of new code (2)


End If


If 


'..... rest of code for option buttons etc., then locking sheet and end.

I hope the above is no too truncated and look forward to some useful pointers, correction, guidance to reach my requirements successfully. Thanks