Posts by Imbuzi

I have tried changing the multiselect on the listbox properties from fmMultiselectSingle to fmMultiselectSingle. However I think I may need an array on this...

Hi

I have recently been working on an addin that allows a user to unhide sheets without having to go to Format>Sheet>Unhide repeatedly. What I would like to do is enable the users to have the ability to select multiple sheets and either Hide or unhide the sheets.

If anyone has any ideas i would be appreciate the help.

TIA

Graeme

Private Sub obVisible_Click()
'   Show Visible Sheets
    Dim wrkSheets() As String
    Dim i As Integer
    Dim wrkSheet As Worksheet
    ListBox1.Clear
    objHide.Enabled = True
    objUnhide.Enabled = False
    
    
    'do this if there are worksheets in the file
    If Worksheets.Count > 0 Then
        'Declare size of array based on the number of worksheets
        ReDim wrkSheets(1 To Worksheets.Count)
    
        'build an array containing then names of all the visible worksheets
        For Each wrkSheet In Worksheets
          'if worksheet visible, store name in array
          If wrkSheet.Visible Then
              i = i + 1
             wrkSheets(i) = wrkSheet.Name
          If wrkSheet.Name <> "" Then ListBox1.AddItem wrkSheet.Name
          
          End If
        Next wrkSheet
    End If
    If ListBox1.ListCount = 0 Then ListBox1.AddItem "(none)"
    ListBox1.ListIndex = 0

Hi Alienscript,

Another way you could do what you are after is to use the following formula,
=IF(ISNA(VLOOKUP(A1,partLU,2,0)),"No Part in Stock",VLOOKUP(A1,partLU,2,0)). the "partLU" is the named range of all of the parts list.

HtH

Graeme

Hi Starman,

Your summation that the links are causing the size of the spreadsheet to be monsterous is correct. I'm not sure of a way to reduce the size but for your second question you could set up a list of all of the tabs on a seperate sheet (controlsheet) that have links. then in the column next to the list insert either a 0 or 1 if you want the data shown on the tabs. Then go to the tabs and where you have a link * that by the 0 or 1 that is on the controlsheet. by doing this you can then control what data is reported and what is omitted.

HTH

Graeme

Hi JK

Sorry to hear about the password problem. take a look at the following link this addin may help you get rid of the locked password.

Graeme

Thanks Andy the character map on my pc at work kept on getting muddled. :o nothing seemed to be matching I've just checked my home machine and came up with the right result as well thanks

Graeme

I have just tried Alt+0249 and it gave me a different character than what was in the character map so you may need to check your character map to see which shape gives you what you are after.

Graeme

Hi Excesspotenial,

Welcome to the Forum...

You can put Bullet points into a cell by using the following key combination Alt+0249

If you want other bullet points have a look at the Unicode character map that is on your computer.

HTH

Graeme

Mo

Another alternative is to use excel's built in database functions have a look at the examples

http://www.ozgrid.com/FreeDown…nctionsWithValidation.zip

HTH

Graeme

Hi Alien

I modified some code that I had that might be of use to you.

I downloaded the gold data from turtletrader and used that to generate the new code...

the only problem that I have encountered is the fact that excel is limited to 65536 rows. and not all of the data will be imported into a single worksheet.
multiple sheets are needed.

As it is currently not possible to attach files drop me a u2u with your address and I'll mail u the file.

Graeme

Hi starman,

You could also try the training products right here on Ozgrid such as the visual basic collection. I found this to be of great help.

http://www.ozgrid.com/Services/VBAExampleList.htm

hth

Graeme

Derk and Weasel,

I have now got the comboBox to work after deleting all of the named ranges and adding them back. I must of had a named range that wasn't 100%

Thanks for your help

Graeme

Egad,

Thanks that really did appeal to my sense of humour. I needed that...

I wonder if haynes will bring out a book on how to repair microsoft application and operating systems?:biggrin:

G

now where did I leave that 4 pound hammer........

Hi Derk and Weasel,

Im currently using '97 but I also have XP on a different PC and I had the same result I could not see all 49 entries. I have checked that the named range was picking up all the entries and I can confirm that it is. I tried setting the row list to various numbers from 5, 10 and 49. The two PC's that I tried it on also have different screen resolution 1280 x 1024 and 800x600.

The only thing that I didn't try was to input the row source and not use a named range.

Graeme

Hi All,

I have come up against this one before but I can't remember how I got round it.
I have a Form comboBox embeded into a spreadsheet that is linked to a named range of 49 items unfortunately it seems that the comboBox is only showing the first 39 Items and is leaving the last 10 out. Is there a property or setting that I need to amend so that it will include all of the items in the list?

TIA

Graeme

Hi Dom,

Have a look at the thread below the formulae translator may be of use to you.
http://www.ozgrid.com/forum/viewthread.php?tid=3185
HTH

Graeme

Hi Dom,

Have a look at the thread below the formulae translator may be of use to you.

HTH

Graeme

He sure does I've book marked the page for those coding emergencies:biggrin: especially when u don't have any reference books handy.;;)

Hi Richie

The second website u posted is brillliant it should be in the thats cool section.

:biggrin:

Makrug,

Thanks for the code I haven't had a chance to look at it as I am a bit busy at the moment but i'll post back as soon.

Baie Dankie

Graeme:biggrin: