Input Data From UserForm In Correct Format in Excel in VBA

ABlohm

New Member
Joined
Mar 16, 2017
Messages
5
I have a UserForm that asks the user to select a day (Monday, Tuesday, Wednesday...) and input a position. I want to direct this information to the worksheet called Data. I want to the position to be inputed in the row that is associated with the day the user selected for it.

The current code I have is:

Dim ColumnCountM As Long
Dim ColumnCountT As Long
Dim ColumnCountW As Long
Dim ColumnCountTH As Long
Dim ColumnCountF As Long
Dim ColumnCountS As Long
Dim ColumnCountSU As Long

ColumnCountM = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountT = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountW = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountTH = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountF = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountS = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count
ColumnCountSU = Worksheets("Data").Range("A1").CurrentRegion.Columns.Count

With Worksheets("Data").Range("A1")

If Me.ComboBoxDay.Value = "Monday" Then
.Offset(0, ColumnCountM).Value = Me.TextPosition.Value
End If
If Me.ComboBoxDay.Value = "Tuesday" Then
.Offset(1, ColumnCountT).Value = Me.TextPosition.Value
End If

If Me.ComboBoxDay.Value = "Wednesday" Then
.Offset(2, ColumnCountW).Value = Me.TextPosition.Value
End If

If Me.ComboBoxDay.Value = "Thursday" Then
.Offset(3, ColumnCountTH).Value = Me.TextPosition.Value
End If

If Me.ComboBoxDay.Value = "Friday" Then
.Offset(4, ColumnCountF).Value = Me.TextPosition.Value
End If

If Me.ComboBoxDay.Value = "Saturday" Then
.Offset(5, ColumnCountS).Value = Me.TextPosition.Value
End If

If Me.ComboBoxDay.Value = "Sunday" Then
.Offset(6, ColumnCountSU).Value = Me.TextPosition.Value
End If

End With


An example of the outcome is:
Monday A C
Tuesday B
Wednesday
Thursday
Friday
Saturday
Sunday


And the desired outcome is:
Monday A C
Tuesday B
Wednesday
Thursday
Friday
Saturday
Sunday


I note that "currentregion.columns.count" is the same for the column counts for all days. I believe this is the main error. How can I fix this to get my desired outcome?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
The format did not come through on the above post.

Current outcome
Monday A C
Tuesday B

Desired outcome
Monday A C
Tuesday B
 
Upvote 0
I wasn't able to format it again, but I want the each row to begin with the column previously, right now it skips a column. For example if I put in Monday it will input in Column B. Next I input Tuesday and instead of going to Column B it will got to Column C. The third input for the example would be in Monday and should be placed in Column C but instead goes into Column D.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top