Posts by Sainaa

Re: Time and data calculations

Thank you Tom.Jones

Re: Time and data calculations

Thank you MR Redli

Hi,

What would be the correct formula or VBA code for total hours calculations.
If we had a meeting started on 1st Nov2016 at 6pm and finished 7.36 and then continued again next day. For example,[TABLE="width: 627"]

Date

Start

Suspend

Date

Reconvene

Finish

Total Hours

[TD="align: right"]1/11/2016[/TD]
[TD="align: right"]6:20:00 p.m.[/TD]
[TD="align: right"]7:36:00 p.m.[/TD]
[TD="align: right"]2/11/2016[/TD]
[TD="align: right"]9:34:00 a.m.[/TD]
[TD="align: right"]11:54:00 a.m.[/TD]
[TD="align: right"]???
[/TD]

[/TABLE]

Re: Choose drop down menu to pull put 2 columns from master data

THANK YOU so much!

Re: Combine 2 codes on one worksheet "Error message

Hi Baraa

Sorry, it is working, thank you very much

S

Re: Combine 2 codes on one worksheet "Error message

How to combine these two codes..please anyone!! Thank you

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("g3:n500")) Is Nothing Then Exit Sub
    Target = Time
    Cancel = True
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("e3:e500")) Is Nothing Then Exit Sub
    Target = Date
    Cancel = True
End Sub

Hi,

How to combine these 2 codes, please help,

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("c2:f500")) Is Nothing Then Exit Sub
Target = Time
Cancel = True
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("b2:b100")) Is Nothing Then Exit Sub
Target = Date
Cancel = True
End Sub

Re: Combine 2 codes on one worksheet "Error message

Hi Baraa Khalil,

Thank you for your reply,
But it does not work!
Nothing happens if I put this code!

Thank you anyway

Hi,
I would like to pull out data automatically in Column A and Column B both by looking at another sheet , in my case"Coding 2" I would like meeting name in the B column.

Have attached my excel spread sheet.

At the moment, I can only have it in Column A with this code.

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim rdata As Range
 Dim c As Range
 If Target.Address = ThisWorkbook.Names("meeting").RefersToRange.Address Then
 'If the dropdown was changed
 Select Case Target.Value
 Case "Hauraki Gulf Forum"
 Set rdata = ThisWorkbook.Names("Hauraki").RefersToRange
 Case "Auckland Domain"
 Set rdata = ThisWorkbook.Names("Domain").RefersToRange
 Case "Regulatory"
 Set rdata = ThisWorkbook.Names("Regulatory").RefersToRange
 Case "Civil Defence and Emergency"
 Set rdata = ThisWorkbook.Names("Defence").RefersToRange
 End Select
 'Set rdata to the range of names to add
 For Each c In rdata.Cells
 If Target.Parent.Cells(65000, 1).End(xlUp).Row < 500 Then 'If there is still space in the table, add it to the last line
 Target.Parent.Cells(65000, 1).End(xlUp).Offset(1, 0).Value = c.Value
 End If
 Next
 End If
 End Sub

Thank you

Moderator Comment: Please use code tags. I have added them for you today.

Hi,

How do you combine these two codes one one sheet?

I am getting all sorts of error messages.

My coding is looking like this!

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("g3:n500")) Is Nothing Then Exit Sub
    Target = Time
    Cancel = True
    ElseIf Not Intersect(Target, Range("E1:E500")) Is Nothing Then
        Target = Date
        Cancel = True
    End If
 Next
End Sub

Thank you

Moderator comment. I have added code tags for you. Please read the forum rules and comply with them in the future.

Re: To Display data in to columns

The first one worked just fine.
Thank you anyway

Sainaa

Re: To Display data in to columns

Thank you Pike,
So clever!!!!

Hi,

I have 1000s or raws of data looking like this,

[TABLE="width: 738"]

Interaction ID

External Reference

External Reference1

External Reference 2

External Reference 3

8140000000

PP

PP

8140000002

RG, LS, LC

RG

LS

LC

8140000030

RG, RFA, ACE

RG

RFA

ACE

[/TABLE]

I would like to sort them like below faster way. How?? Thank you
[TABLE="width: 220"]

[TD="class: xl65, width: 111, bgcolor: transparent"]8140000000[/TD]
[TD="class: xl66, width: 183, bgcolor: transparent"]PP[/TD]

[TD="class: xl65, bgcolor: transparent"]8140000002[/TD]
[TD="class: xl66, bgcolor: transparent"]RG[/TD]

[TD="class: xl65, bgcolor: transparent"]8140000002[/TD]
[TD="class: xl66, bgcolor: transparent"]LS[/TD]

[TD="class: xl65, bgcolor: transparent"]8140000002[/TD]
[TD="class: xl66, bgcolor: transparent"]LC[/TD]

[TD="class: xl65, bgcolor: transparent"]8140000030[/TD]
[TD="class: xl66, bgcolor: transparent"]RG[/TD]

[TD="class: xl65, bgcolor: transparent"]8140000030[/TD]
[TD="class: xl66, bgcolor: transparent"]RFA[/TD]

[TD="class: xl65, bgcolor: transparent"]8140000030[/TD]
[TD="class: xl66, bgcolor: transparent"]ACE

[/TD]

[/TABLE]

Re: VBA Error "Ambiguous name detected" on Worksheet_Change event

Hi SO,

Much appreciated,
Thank you for explaining.

Sainaa

Why I am getting error message "Ambiguous name detected": Worksheet Change

How can I fix it?

The below here is my 3 coding

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim rdata As Range
 Dim c As Range
 If Target.Address = ThisWorkbook.Names("meeting").RefersToRange.Address Then
 'If the dropdown was changed
 Select Case Target.Value
 Case "Environment and Community"
 Set rdata = ThisWorkbook.Names("Environment").RefersToRange
 Case "Audit and Risk"
 Set rdata = ThisWorkbook.Names("Audit").RefersToRange
 Case "Planning"
 Set rdata = ThisWorkbook.Names("Planning").RefersToRange
 Case "Hauraki Gulf Forum"
 Set rdata = ThisWorkbook.Names("Hauraki").RefersToRange
 Case "Auckland Domain"
 Set rdata = ThisWorkbook.Names("Domain").RefersToRange
 Case "Regulatory"
 Set rdata = ThisWorkbook.Names("Regulatory").RefersToRange
 Case "Civil Defence and Emergency"
 Set rdata = ThisWorkbook.Names("Defence").RefersToRange
 Case "Appointments and Performance"
 Set rdata = ThisWorkbook.Names("Appointments").RefersToRange
 Case "Strategic Procurement"
 Set rdata = ThisWorkbook.Names("Strategic").RefersToRange
 Case "Governing Body"
 Set rdata = ThisWorkbook.Names("Governing").RefersToRange
 Case "Community Development"
 Set rdata = ThisWorkbook.Names("Community").RefersToRange
 End Select
 'Set rdata to the range of names to add
 For Each c In rdata.Cells
 If Target.Parent.Cells(65000, 1).End(xlUp).Row < 1000 Then 'If there is still space in the table, add it to the last line
 Target.Parent.Cells(65000, 1).End(xlUp).Offset(1, 0).Value = c.Value
 End If
 Next
 End If
 End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("g3:n500")) Is Nothing Then Exit Sub
    Target = Time
    Cancel = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E1:E500")) Is Nothing Then Exit Sub"
    Target = Date
    Cancel = True
End Sub

Re: Choose text from drop down list will populate lists of texts automaticaly in next

I see, did not know there is a feedback button as I recently joined to this website!! Now I know!
THANK YOU big big time!

Take care
Sainaa

Re: Choose text from drop down list will populate lists of texts automaticaly in next

Perfect, THANK YOU!
I owe you a drink!
Are you coming up to Guns'N'Roses concert this weekend?

THANK YOU,

Re: Choose text from drop down list will populate lists of texts automaticaly in next

Hi,
Where is the word "Fill"
I tried to change the word from Fill to no Fill from your code, but cant find it.
And my name list still stops at Column A23 even though there is no colouring...

My code looks like this .

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rdata As Range
Dim c As Range
If Target.Address = ThisWorkbook.Names("meeting").RefersToRange.Address Then
'If the dropdown was changed
Select Case Target.Value
Case "Environment and Community"
Set rdata = ThisWorkbook.Names("Environment").RefersToRange
Case "Audit and Risk"
Set rdata = ThisWorkbook.Names("Audit").RefersToRange
Case "Planning"
Set rdata = ThisWorkbook.Names("Planning").RefersToRange
End Select
'Set rdata to the range of names to add
For Each c In rdata.Cells
If Target.Parent.Cells(65000, 1).End(xlUp).Row < 23 Then 'If there is still space in the table, add it to the last line
Target.Parent.Cells(65000, 1).End(xlUp).Offset(1, 0).Value = c.Value
End If
Next
End If
End Sub

Re: Choose text from drop down list will populate lists of texts automaticaly in next

Thank you,
I removed the "*" and it works fine, THANK YOU.
However I do NOT need any colouring in A column, also Name repeats condition is not required as some attendees attend more than 1 meetings. So actually I need names repeat is ok condition!!?
I am getting there with your help...

Re: Choose text from drop down list will populate lists of texts automaticaly in next

Hiya,

I've followed your instructions, and what I get is when Ichange drop down, then name list appear at the bottom A16 SHEET1 and it does not add names if I change the drop down again...!!? if I choose another drop down again, nothing happens unless I delete the already populated names..?!