Posts by bimmy80

Re: Assistance to enter date in a specific format in Userform Textbox

Hi royUK,

I tried the code provided but nothing happens.

In textbox1 entered dates as below, but nothing happens -

25Jan
25Jan17
25Jan2017

I'm a rookie in vba.

I changed the code as below... but no success

dt = format(CDate(.TextBox1.Value) + Val(.TextBox2.Value),"ddmmm") ''/// change the format as required

The idea is to enter date in either of the 3 formats specified below in textbox1, which then gets selected on the calendar on the userform.

dd
ddmmm
ddmmmyy

What part of the code should be changed to achieve this.

Re: Assistance to enter date in a specific format in Userform Textbox

Hello Excel Gurus,

As stated above the code works exactly as I want.

I'm looking for an addition to the above code that allows me to manually enter dates in textbox1 in specific format.

The format I would like to use are -

dd
ddmmm
ddmmmyyy

Below is a code that I came across while searching through this site but not sure how to adapt it to my requirement -

Private Sub txDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 
    Dim dDate As Date 
    dDate = DateSerial(Year(Calendar1), Month(Calendar1), Day(Calendar1)) 
    dDate = txDate.Value 
    txDate = Format(dDate, "dd/mmm/yy") 
    Sheet1.Range("A1").Value = txDate.Value 
End Sub

I would really appreciate if someone can help me out.

The idea is to enter date in either of the 3 formats specified above in textbox1, which then gets selected on the calendar on the userform.

Hi,

I have a code that allows me to enter date in first textbox which then gets selected in the calendar. There is a second textbox that allows me to add or subtract dates. The code works perfectly.

Userform Code -

Option Explicit
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsDate(Me.TextBox1.Value) Then Me.Calendar1.Value = Me.TextBox1.Value
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim dt As Date
    With Me
        If IsDate(.TextBox1.Value) Then
            dt = CDate(.TextBox1.Value) + Val(.TextBox2.Value)
            .TextBox1.Value = dt
            .Calendar1.Value = dt
        End If
    End With
End Sub

Currently the date is entered in below formats -

16 Jan ===> (Space between 16 and Jan)
16 Jan 17 ===> (Space between 16 and Jan and 17)
16 Jan 2017 ===> (Space between 16 and Jan and 2017)

I would like to enter dates in the textbox without any spaces -

16Jan
16Jan17
16Jan2017

Have attached example sheet.

Kindly assist.

Re: Macro to Highlight a Range

I'm good with formulas but not with macros.

Anyways thanks.

Re: Macro to Highlight a Range

Let me be more specific.

Certain datas will be pasted in Range C which will contain above mentioned Ticket Ranges.

As soon as the data is pasted in Range C, it should get highlight in RED if it contains the Ticket Ranges.
(Without pressing ALT+F8)

Ticket Range start with "ST388 - ST404" and "CR510 - CR528". Its Alpha Numeric.

Since the data will be maintained across multiple sheets, macro should work on all sheets in the workbook

Have attached sample sheet with dummy data.

Re: Macro to Highlight a Range

The thing is data is maintained sheet-wise. I have currently around 30 sheets and additional sheets will be added in future.
So using formula and conditional formatting is out of the question.

Hi,

Looking for a macro that will highlight Range C if it contains below Ticket Ranges.

ST388 - ST404
CR510 - CR528

What the macro should do -

If Range C contatins ticket ranges from ST388 to ST404 and CR510 to CR528, then,
Range C should get highlighted with RED color .

Option should be provided in the code to make changes to the Ticket Range and color.

Re: Find Cell from ComboBox Value and write to adjacent cell

Any help people

Re: Find Cell from ComboBox Value and write to adjacent cell

Sorry, since the size of the files were big have to make few changes

Below is the code which I got from the net

Private Sub cmdAdd_Click()
Dim iRow As Long, iCol As Long




If cmbDate = "" Then
    MsgBox "please select a date"
    cmbDate.SetFocus
    Exit Sub
End If
If cmbCat = "" Then
    MsgBox "please select a category"
    cmbCat.SetFocus
    Exit Sub
End If
If txtAmount = "" Then
    MsgBox "please enter then amount"
    txtAmount.SetFocus
    Exit Sub
End If




With ActiveSheet
    iRow = .Range("A:A").Find(cmbDate.Value, LookIn:=xlValues, LookAt:=xlWhole).Row
    iCol = .Range("3:3").Find(cmbCat.Value, LookIn:=xlValues, LookAt:=xlWhole).Column
    .Cells(iRow, iCol) = .Cells(iRow, iCol) + txtAmount
End With




'clear the data
    cmbDate.Value = ""
    cmbCat.Value = ""
    txtAmount.Value = ""
    cmbDate.SetFocus




End Sub




Private Sub cmdDone_Click()
    Unload Me
End Sub




Private Sub UserForm_Initialize()
Dim cItem As Range




Dim ws As Worksheet




Set ws = ActiveSheet
For Each cItem In ws.Range("3:3").SpecialCells(xlConstants).Offset(, 0)
    With Me.cmbCat
        If cItem <> "" And cItem <> "Total" Then .AddItem cItem.Value
    End With
Next cItem




For Each cItem In ws.Range("A:A").SpecialCells(xlConstants, xlNumbers)
    With Me.cmbDate
        .AddItem cItem.Value
    End With
Next cItem




End Sub

What the above code do -

Using the date value from cmbDate, find this text in Row A of worksheet Simon
and write the Value of txtAmount in a cell adjacent to the date and under the cell
equal to the cmbCat value when the CommandButton1 is pressed.

Refer attached workbook Household Planner to get better understanding of above explanation

I have made another workbook Production Time containing above mentioned macro and the Userform.
To this Userform I have added below buttons -

Goto - commandbutton
Counts - textbox

And, have re-named 1 textbox to Time

I need codes for above 3 buttons to be added to the existing code.

The code should do the following -

Goto command button

With date and category selected from the 2 combobox, when clicked on Goto command button it should take me to the Time column under Category Alcohol/Cigaretes of Date 01-May-15.

Below snapshot will give an idea

[ATTACH=CONFIG]65234[/ATTACH]

Time and Counts Texbox

For textboxes Time and Counts the code should do the following -

Agents will select data from both the combo boxes and manually enter data into both the
text boxes.

When command button Enter is clicked the data entered in both the text boxes should reflect under the headings Time and Counts column in the excel sheet and also under the correct category and for the said date.

Below snapshot will give an idea

[ATTACH=CONFIG]65235[/ATTACH]

Note : Sometimes data will only be entered in textbox Counts. In such case macro should still update relevant columns.

It would be helpful if you can attach your finished work as I'm new to Userforms.

Also, workbook Production Sheet is where I need the changes/additions to be made.

Re: Macro to copy data from Main File sheet and Update the DBR Report Sheet

PERFECT...

Jindon... THANKS A TONNE... U ROCK...[Blocked Image: http://www.sherv.net/cm/emoticons/hand-gestures/rock-on-smiley-emoticon.gif][Blocked Image: http://www.sherv.net/cm/emoticons/hand-gestures/rock-on-smiley-emoticon.gif][Blocked Image: http://www.sherv.net/cm/emoticons/hand-gestures/rock-on-smiley-emoticon.gif]

Re: Macro to copy data from Main File sheet and Update the DBR Report Sheet

Code not updating for below columns... Kindly help... PLEASE

Data in Column J in Main File is not getting updated in Column F in DBR Report
Data in Column C in Main File is not getting updated in Column I in DBR Report

Re: Macro to copy data from Main File sheet and Update the DBR Report Sheet

Let me get back to U

Re: Macro to copy data from Main File sheet and Update the DBR Report Sheet

Pleaseee....

Re: Macro to copy data from Main File sheet and Update the DBR Report Sheet

It does everything exactly as I want... Except for one thing.

When I click on button US it updates as UA and not US.

Request you to remove the password.

Re: Macro to copy data from Main File sheet and Update the DBR Report Sheet

Few columns are missing

[TABLE="width: 500"]

From Main File

To DBR Report

Column J

Column F

[/TABLE]

[TABLE="width: 500"]

From Main File

To DBR Report

Column D

Column C (Explanation below)

[/TABLE]

Column D contains Numbers which which should be copied to DBR Report in a different format

Examples as below

Main File - Column D
[TABLE="width: 500"]

01

[/TABLE]

Macro should update Column C in Template (DBR Report Sheet)
[TABLE="width: 500"]

1

[/TABLE]

Main File - Column D
[TABLE="width: 500"]

012

[/TABLE]

Macro should update Column C in Template (DBR Report Sheet)
[TABLE="width: 500"]

12

[/TABLE]

Main File - Column D
[TABLE="width: 500"]

0123

[/TABLE]

Macro should update Column C in Template (DBR Report Sheet)
[TABLE="width: 500"]

123

[/TABLE]

Main File - Column D
[TABLE="width: 500"]

1234

[/TABLE]

Macro should update Column C in Template (DBR Report Sheet)
[TABLE="width: 500"]

1234

[/TABLE]

Also, can something be done for point 7 (From 1st post)

Re: Macro to copy data from Main File sheet and Update the DBR Report Sheet

Weekdays are distributed into numbers -

Mon - 1
Tue - 2
Wed -3
Thur - 4
Fri - 5
Sat - 6
Sun - 7

Column in the Template (DBR Report Sheet) is also distributed accordingly -

J - 1
K - 2
L - 3
M - 4
N - 5
O - 6
P - 7

What the macro should do -

Column N in Main File
[TABLE="width: 500"]

17

[/TABLE]

As explained above macro should update respective columns
[TABLE="width: 500"]

[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]7[/TD]

[/TABLE]

Column N in Main File
[TABLE="width: 500"]

234

[/TABLE]

As explained above macro should update respective columns
[TABLE="width: 500"]

[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]

[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[/TABLE]

Column N in Main File
[TABLE="width: 500"]

56

[/TABLE]

As explained above macro should update respective columns
[TABLE="width: 500"]

[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]

[/TABLE]

Note :
Numbers will come in different combinations, whatever the combinations, macro should update them under respective columns (As explained above)

Re: Macro to copy data from Main File sheet and Update the DBR Report Sheet

Staff will sort and paste the data to Main File.
DBR Report contains a template.

What I'm looking for is, the macro should copy data from Main File to the template (in the DBR Report).

Template has 2 columns which are already updated with certain data as below -

[TABLE="width: 500"]

Column A

Column B

FR:

BA

TO:

BA

FR:

BA

TO:

BA

[/TABLE]

For explanation as to how macro should paste the data,
refer to points under heading - The macro should do the following - (Underlined) from above post.

Have attached sample sheet for better understanding

A workbook named "Report" has 2 sheets "Main File" and "DBR Report".

After sorting the data, it is pasted to the Main File .

The DBR Report has 2 Columns already updated with data which are -

Column A reflects as below (One below the other) :
FR:
TO:
FR:
TO:
FR:
TO:

Column B reflects BA from Cell B4 onwards.

The macro should do the following :

1) Copy data from Main File to DBR Report to rows reflecting FR:
Leaving Rows reflecting TO: as blanks
(Simply put the data should be copied to rows which reflect FR: in DBR Report.

2) Data should be copied as below :

[TABLE="width: 500"]

From Main File

To DBR Report

Column A

Column R

Column B and C

Column H and I (Explanation below - a)

Column D

Column C (Explanation below - b)

Column F

Column D

Column H

Column E

Column J

Column F

Column L

Column G

Column N

Column J, K, L, M, N, O and P (Explanation below - c)

[/TABLE]

a) Column B and C contains date which should be copied to DBR Report in a different format

[TABLE="width: 500"]

From Main File

To DBR Report

20150828

28-Aug-15

20150831

31-Aug-15

[/TABLE]

b) Column D contains Numbers which which should be copied to DBR Report in a different format

[TABLE="width: 500"]

From Main File

To DBR Report

0001

1

0012

12

0123

123

1234

1234

[/TABLE]

c)Column N contains weekdays in number format, i.e Mon to Sun will be 1 to 7
which should be copied to DBR Report under Columns J, K, L, M, N, O and P

[TABLE="width: 500"]

Column O in Main File

17

234

56

[/TABLE]

How above data should reflect in DBR Report
[TABLE="width: 500"]

J

K

L

M

N

O

P

1

7

2

3

4

5

6

[/TABLE]

7) Since the report is generated for 2 agents (code is AA and US) need two buttons,one for AA and one for US.

Clicking on AA button will -
a) Update data in DBR Report as mentioned above and Column Q reflecting as AA from Cell 4 Onwards

Clicking on US button will -
b) Update data in DBR Report as mentioned above and Column Q reflecting as US from Cell 4 Onwards

Sample sheet has been attached to give an idea how Main File looks like and DBR Report should look like
after the macro is run.

Re: Macro to create New Workbooks,Rename and Update Data

I just missed out on 1 additional point.

Being a Newbie not good with MACRO... Kindly Assist.

Extremely sorry for this. Going forward will be EXTRA ALERT to add all the required points.