Posts by zjamespryor

I've attached a much earlier version of the "Calendar" that I'm working on that includes the things I mentioned when I mentioned the "Calendar" and "October sheet" before. This includes the same code that you guys have been helping me with and it'll help show why I need to go across both rows and columns. This doesn't include any locations yet, it's just the most basic version.

I apologize for the confusion, I don't think I did a very good job of setting up my initial example.

Quote from Justin Doward

No need from my perspective to post further detail, but it would be good if you could clarify whether you 1. require code to fill every blank cell in a range, stepping across to the next blank column where required and stopping once you match the textbox value, or 2. whether you just need to find the textbox value and fill the first available column in the matched row... The second I believe you can do without a loop ... the first I would be interested to know if you can achieve without a loop.

The first. My above code finds the respective reference date (I'm matching a number here because its easier to match than a date), then offsets to the column where it should begin pasting. If it sees that there is no value in the respective cell then it'll paste the text box value. If it sees that there is a value, it'll move one column to the right. If it sees that the textbox has the same value, it'll move down to the next row.

Again, thank you both. I'll try both of these!

Hello gents, so my "master" workbook is very involved. I have "groups of guests" arriving on certain dates and leaving on other dates. On my master workbook the above code posts both to a sheet with the same name as the value in the combo box and both to a master dates sheet. The master dates sheet is setup in a similar fashion to the sheets in the examples.

I have another sheet setup to look like the month of October. On any given day in October I've set that day's cell equal to the respective cell containing the counta on the master dates sheet. So after a team is added they could look at the month of October and see "oh, there are five groups coming from October 1st to October 4th." The point of it posting into a row is for a macro that will show all the teams coming on that day when ran. So not only can the client see at a glance, "oh there are five groups coming from October 1st to October 4th, but if I click this button I can see the particular teams on October 1st, 2nd etc." The different locations come in so not only can I see what teams are coming in, but also where they'll be staying.

I can give you the "master" if you would like, but it'll be tomorrow at the soonest since I'll have to remove some information.

Hello gents, thank you for the replies!

Quote from royUK

Do you mean if the value is not found then enter the value from TextBox2?

Yes. If the cell is blank, then insert the value from the textbox. If it's not blank, then move to the next cell to the right and do the same.

Justin I'll give your code a go and see if I can get it to work!

Hello,

I'm trying to improve my vba skills and I came out across this article from ozgrid on speeding up macros. I've looked at my own code and cleaned up some things like changing "" into vbNullString, but the two things that I don't know how to use from this article are changing If then into booleans and how not to use "select" with the code that I am running. The code in question is below:

Private Sub CmdBtn_Enter_Click()


Dim CorrectRow, i, q As Long


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False


For i = 1 To 3




If Me.Controls("ComboBox" & i) <> vbNullString Then


CorrectRow = WorksheetFunction.Match(CInt(DateDiff("d", "Oct 1, 2020", Me.TextBox1)), Sheets(Me.Controls("ComboBox" & i).Value).Range("A2:A213"), 0) - 1 'this is matching the date difference to a reference number on the respective worksheet


With Sheets(Me.Controls("ComboBox" & i).Value)
.Select


For q = 0 To DateDiff("d", "Oct 1, 2020", Me.TextBox1)
.Range("A2").Offset(CorrectRow + q, 3).Select
Do Until ActiveCell.Value = Me.TextBox2 'Loops the cells until the cell equals your text
If ActiveCell.Value = vbNullString Then        'checks blank cells
ActiveCell.Value = Me.TextBox2  'inserts your text if blank
ElseIf ActiveCell.Value <> vbNullString Then   'if its not blank
ActiveCell.Offset(0, 1).Select   'offset the cell by one, allows loop to repeat to find a blank cell
End If
Loop
Next q
End With
End If
Next i


Sheet1.Select


Unload Me


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Thank you for any help!

Great, thank you!

Quote from royUK

It's fixed at zero. Why is this necessary?

This is what I'm trying to avoid:

If Me.Txt_DateNew7.Value = "" Then
Me.MultiPage5.Pages(0).Visible = False
End If


If Me.Txt_DateNew8.Value = "" Then
Me.MultiPage5.Pages(1).Visible = False
End If


If Me.Txt_DateNew9.Value = "" Then
Me.MultiPage5.Pages(2).Visible = False
End If


If Me.Txt_DateNew10.Value = "" Then
Me.MultiPage5.Pages(3).Visible = False
End If


If Me.Txt_DateNew11.Value = "" Then
Me.MultiPage5.Pages(4).Visible = False
End If


If Me.Txt_DateNew12.Value = "" Then
Me.MultiPage5.Pages(5).Visible = False
End If


If Me.Txt_DateNew13.Value = "" Then
Me.MultiPage5.Pages(6).Visible = False
End If

        If Me.Txt_DateNew14.Value = "" Then
            Me.MultiPage6.Pages(0).Visible = False
        End If
        
        If Me.Txt_DateNew15.Value = "" Then
            Me.MultiPage6.Pages(1).Visible = False
        End If
        
        If Me.Txt_DateNew16.Value = "" Then
            Me.MultiPage6.Pages(2).Visible = False
        End If
            
        If Me.Txt_DateNew17.Value = "" Then
            Me.MultiPage6.Pages(3).Visible = False
        End If
        
        If Me.Txt_DateNew18.Value = "" Then
            Me.MultiPage6.Pages(4).Visible = False
        End If
    
        If Me.Txt_DateNew19.Value = "" Then
            Me.MultiPage6.Pages(5).Visible = False
        End If
        
        If Me.Txt_DateNew20.Value = "" Then
            Me.MultiPage6.Pages(6).Visible = False
        End If

I figured that If I could get the page index to start at the same number as the text box number then I could loop through the code quickly rather than having to copy and paste it.

Hello!

I'm wondering if there is a way to change the index of a multipage to start at a number other than zero. For example, I can run a loop through text boxes I have on the my first page so that I can hide the page if a certain text box is empty. Example code:

For i = 0 To 6


If Me.Controls("Txt_DateNew" & i).Value = "" Then
Me.MultiPage4.Pages(i).Visible = False
End If


Next i

I'd like to start the index of my second page at 7 so I can continue looping through all pages on the user form, but unfortunately I can't manually change the index to start at 7.

I appreciate any help!

Thanks!

Yeah, this is significantly better. Thank you!

The RowFinder was code I had deleted because it wasn't working, but I realized (probably when you were helping me) that I had a typo in the original formula for the wrong column number.

Where would I put it as code in the user form? Under UserForm_Initialize and just list out each object?

Quote from royUK

Which userform are you cleaning?

Why are you not using the ControlTipText? It would save a lot of coding.

Code

Me.Txt_TeamCode.ControlTipText = "Insert team code here"

I'm trying to cleanup the AddNew_UF userform. Where would I put that code and how is it used?

Hello,

I've got a pretty complex user form that is sending information to different rows on different sheets. I'm trying to "clean" my code by making it easier to look at it, but what I'm doing is making the select code not work at all. I'm a novice with VBA and I'm trying to improve my skills! I appreciate any help!

This works:

[VBA]       Dim DateRow As Integer
            DateRow = WorksheetFunction.Match(Range("ArrivalDate"), Sheets("Dates").Range("Dyn_SelectDate"), 0) - 1 'selects the the same date in the calendar
        Dim MemberRowAdd As Integer
            MemberRowAdd = Range("Dyn_TeamMembers")


        Dim i As Integer
            For i = 0 To Range("DateDifference")


                Sheets("Dates").Range("Data_Start_Dates").Offset(DateRow + i, 1).Value = Sheets("Dates").Range("Data_Start_Dates").Offset(DateRow + i, 1).Value + 1 'loops through the rows to add 1 more value to that date range for the team column
                Sheets("Dates").Range("Data_Start_Dates").Offset(DateRow + i, 2).Value = Sheets("Dates").Range("Data_Start_Dates").Offset(DateRow + i, 2).Value + MemberRowAdd 'does the same for the team member column but adds team members
            Next i[/VBA]

This does not:

[VBA]        Dim DateRow As Integer
            DateRow = WorksheetFunction.Match(Range("ArrivalDate"), Sheets("Dates").Range("Dyn_SelectDate"), 0) - 1 'selects the the same date in the calendar
        Dim MemberRowAdd As Integer
            MemberRowAdd = Range("Dyn_TeamMembers")


        Dim i As Integer
            For i = 0 To Range("DateDifference")


        Dim RowFinder As Integer
            RowFinder = Sheets("Dates").Range("Data_Start_Dates").Offset(DateRow + i, 1).Value
                RowFinder = Sheets("Dates").Range("Data_Start_Dates").Offset(DateRow + i, 1).Value + 1 'loops through the rows to add 1 more value to that date range for the team column
                RowFinder = Sheets("Dates").Range("Data_Start_Dates").Offset(DateRow + i, 2).Value + MemberRowAdd 'does the same for the team member column but adds team members
            Next i[/VBA]

Everything is the same between the two except for the last section of code.

I tried [VBA]Dim RowFinder as String[/VBA] but that didn't work either.

Am I setting my variables improperly or is what I'm trying to do impossible?

Thanks!

Thank you! That did it! Slapped myself when I saw what the issue was

If I try this:

[VBA]Option Explicit
Private Sub Txt_Depart_AfterUpdate()
Dim textYear As Date
Dim textYear2 As Date


    textYear = CInt(Me.Txt_Arrival.value)
    textYear2 = CInt(Me.Txt_Depart.value)
    If textYear > textYear2 Then
        Msgbox "Departure date is before arrival date"


End If
    Exit Sub


End Sub[/VBA]

It says I get a type mismatch error. Am I setting up my variables wrong? Thank you for helping me with this!

Still getting the issue of it saying that "Variable is not defined"

I am trying to figure out how to make a message box display for a date if it is earlier than another date. These are for "Arrival" and "Departure" dates so I would like a message box to show if the departure date is before the arrival date (think that I'm hosting people, not sending them off :)). The dates are in text boxes on a user form. I only have a little bit of experience with VBA but I really enjoy using the program so I appreciate any help I can get!

I've tried this code:

[VBA]Option Explicit
Private Sub CmdBtn_Add_Click()
Dim DateDepart As Date
Dim DateArrive As Date
DateDepart = Txt_Depart.value
DateArrive = Txt_Arrive.value


If DateDepart.value < DateArrive.value Then
    MsgBox "Departure date is before arrival date", vbCritical, "Incompatible Departure Date"
    
Exit Sub
End If
End Sub[/VBA]

but I keep getting "Compile Error: Variable not defined."

I created a practice user form where I can get the following to work:

[VBA]Option Explicit
Private Sub CommandButton1_Click()
If TextBox1.value > TextBox2.value Then
MsgBox "Number is bigger!"


End If


End Sub[/VBA]

which works fine regardless of whether it is in a date format or not.