Posts by xlite

Re: Combo Boxes

hi Z,

first of all i don't think you can multiselect a combobox,
you can achieve that with a listbox

in VBE, place a listbox on a userform and set it in the Properties > Multiselect >
1-MulltiSelectMulti

double click the userform and place this module:

Private Sub UserForm_Initialize()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    With Me.ListBox1
        .AddItem ws.Name
    End With
Next
End Sub

hope this helps

Re: Right-click menu when clicking on Chart elements

hi m,

when you created the right click menu, most probably you used:

application.commandbars("Cell").controls.add......

this works only in cells
in charts you should use:

application.commandbars("object/plot").controls.add......

or

application.commandbars("plot area").controls.add......

can't remember which one, pls try it out

so u should have the menu ob both cell and chart commandbars

hth

Re: Staffing Calculator

hi,

try this one

hth

Re: Find In Between Values

thank you all for the nice solutions,

i'm still an idiot where array formulas are concerned :p
thanks again

hi guys,

i need to find the nearest result,
for example if i have 230 i'll get B
or if i have 460 the answer would be E

100 A
200 B
300 C
400 D
500 E

can anyone suggest a formula for this?
any help is appreciated, thanks in advance :smile:

Re: Matching Coloured Cells To Created Graph

ah sorry,

i din't notice that there are different labels.

so what i'd do is to have common labels and use Vlookup to get the data
have a look at the attched

hth

Re: Matching Coloured Cells To Created Graph

hi ports,

what you should do is organize the data separately
pls refer to attached file

hope this helps

Re: Header That Is Generated From A Cell

hi J,

try this code:

With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ThisWorkbook.Sheets(1).Range("D4")
        .RightHeader = ""


    End With

hth

Re: Code To Write Code For Workbook Open Event

hi Kj,

i modified Chip Pearson's code a litte to suit yours
see his website here:
http://www.cpearson.com/excel/vbe.htm

Quote

Before using these procedures, you'll need to set a reference in VBA to the VBA Extensibility library

Sub AddProcedure()


Dim VBCodeMod As CodeModule
Dim LineNum As Long


Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
With VBCodeMod
    LineNum = .CountOfLines + 1
    .InsertLines LineNum, _
"Sub Workbook_Open()" & Chr(13) & _
" Msgbox ""Here is the new procedure"" " & Chr(13) & _
"End Sub"
End With




End Sub

hth

Re: Validation To Populate Customer Details Automatically

hi irfannaseem,

welcome to Ozgrid.com

you can use Vlookup for the same purpose
have a look at the attached file and post back if in doubt

hope this helps

Re: Line Chart Showing Values Over Time Intervals

hi bradles,

change the chart type to XY Scatter,
in the X axis, change the scale to 0.000694
ie: 1/(24hrs*60min)

hth

Re: Default Addin Location?

Quote from mtlay

Code

'   Under ReformatVendorData (Reformat Vendor Data.xla)
'   In Microsoft Excel Objects|This Workbook


Private Sub Workbook_AddinInstall()
Call MenuCommand
End Sub


Private Sub Workbook_AddinUninstall()
Call MenuCommand_Remove
End Sub

Display More

i see you still dont have this code i suggested earlier in ThisWorkbook module:

Private Sub Workbook_Open() 
    MenuCommand 
End Sub

why dont you give it a try?

Re: Automatically Update Master Worksheet From Departmental Worksheets

Quote from CBishop

The approach you use in that example file could work if it would clear the master sheet before adding data; but as is, it would make hundreds of duplicate entries each time it's run.

yup, you're right
i've included a line to clear the data in the Master List
and adapted the code to yours

just something i don't understand; is col A left empty?

Sub Summarize()
Dim ws As Worksheet
Dim lastRng As Range
Application.ScreenUpdating = False 'speed up code


ThisWorkbook.Sheets("Master List").Rows("2:65536").ClearContents 'clear


For Each ws In ThisWorkbook.Worksheets
Set lastRng = ThisWorkbook.Sheets("Master List").Range("B65536").End(xlUp).Offset(1, 0)


    Select Case ws.Name
        Case "Master List" 'exlude
        'do nothing
        Case Else
        ws.Activate
                
        'copy data from individual sheets
        Range("B2", Range("X65536").End(xlUp)).Copy lastRng


    End Select
Next
Application.CutCopyMode = False 'clear clipboard
Application.ScreenUpdating = True
Sheets("Master List").Activate
End Sub

Re: Automatically Update Master Worksheet From Departmental Worksheets

hi CBishop,

welcome to Ozgrid.com

don't worry abt being a newbie, just take yr time reading the posts in Ozgrid
and be sure to abide by the rules :p

attached is a sample for consolidating sheets into one.
have a look at it, study it and post back if you have any questions

Re: Convert Number To Hours & Minutes

Quote from Timbo

Everyone: Thanks for all the advice I'll kick it back and get him to do some re-formatting.

ok, but doesn't my formula work?:(

Re: Inventory Tracking

Quote from lioncourt

is there a way to make vlookup non-alphabetical

i believe Vlookup works for numbers, aplphabets or combination of those

as long as what you look for is exactly the same as the source data.

i notice that yr inventory list reads 3/4 ply instead of 3/4" PLY
this of course won't work :p

Re: Default Addin Location?

ah got it

place this code in ThisWorkbook module:

Private Sub Workbook_Open()
MenuCommand
End Sub

this will add the menu each time the excel opens

Re: Inventory Tracking

creating inventories can be very tricky, especially if you are not familiar with all the items.

what i can suggest is to use Vlookup formulas

Re: Convert Number To Hours & Minutes

Quote from Timbo

A colleague has columns of data that when summed are all wrong i.e. 8.65 instead of 9 Hours 5 minutes.

hi Timbo,

what you should do is correct the mistake yr colleague did before summing them up

try this formula to convert them to time
then you should be able to get the correct sum:

=SUBSTITUTE(A1,".",":")

where A1 is the wrong entry.

hth

Re: Default Addin Location?

Quote from mtlay

The addin has a dropo down "Data" menu item associated with it. It has no optional submenu items. It's more of a menu function.

sorry i still can't get it,
is it possible if you post the menu code here?
(pls remember to use the code tags using the # icon)
thanks