Posts by Excel newbie

Re: Colorfunction Not Calculating When Conditional Formating Cell Colour

What exactly is your colour function formula, could you post that ?

Re: Select Case Not Looping

My pleasure, glad we could get it figured out :smile:

Re: Recording And Totaling Data From A Drop Down Menu

Your spreadsheet wasnt attached. I would think though if you are trying to get a count you are currently using the COUNT function ? If you use COUNTA it will pick up words and numbers...

If this doesnt help post up the spreadsheet and we will have a look![hr]*[/hr] Auto Merged Post;[dl]*[/dl]hmm now the spreadsheet is attached...how odd i couldnt see it before!!

Anyway let me know if my post helped

Re: Displaying An Error Then Exiting Macro

Try something like the below :

If (lLastRow < 3) Then 'lLastRow is a  variable used in a  loop to  count how many rows there are starting at the top.
     MsgBox "Not Enough Data", vbOKOnly, "Data"
     Exit Sub
End If

Re: Select Case Not Looping

Try something like the below:

Sub test()
Dim ws As Worksheet


For Each ws In Worksheets
Select Case LCase(ws.Name)
Case "main", "reconcile", "update", "outbal", "test"
'do nothing
Case Else
Sheets(ws.Name).Range("B2").Copy Destination:=Sheets("TEST").Range("B65536").End(xlUp).Offset(1, 0)


End Select
Next
End Sub

Re: Select Case Not Looping

The below does the actual looping :

For Each ws In Worksheets

Next ws

If you are trying to check each worksheet (upper case)name, you would use:

Select Case UCase(ws.name)

instead of

Select Case UCase(wsLoop)

Let me know how you go!

Re: Vlookup Returns Zero

Hi Dradumont

When using a vlookup in 99% of the cases the parameters are as below:

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

(have a look in excel help on vlookup for explanation of this)

Therefore your formula would be:

=IF($B25="","",VLOOKUP($B25,'[INVENTORY MASTER.xls]INVENTORY'!$A$4:$L$3356,3,FALSE))

Does this make a difference ?

Re: Sort By Cell Pattern

Cross posted on excelforum :
http://www.excelforum.com/showthread.php?t=607753

Hi All!

I have a question, I am using the calendar control on a form of mine, which i believe requires ms access to be installed to use(from what ive read). This is fine as most of the PC's it will be run on have this installed. However if it is run on a PC that doesnt have ms access it gives me the "Could not load an object because it is not avaliable on this machine" and then "cant find project or library error". How can I trap this error ? Ive tries whe the workbook opens, and when the form initializes but it just brings up that error :yikes:

Any help would be appreciated!

This has also been posted on excelforum:
http://www.excelforum.com/showthread.php?t=606270

Re: Add New Sheet Vba

Also posted in excelforum.com

Re: MoveFolder. Run-time Error 76, Path Not Found

I often have problems with commas contained in a path, see the below excerpt from http://www.ozgrid.com/forum/showthread.php?t=69423

Quote

Just as an aside if you dont want to change the folder name, you may try changing :

strRootPath = "J:\MyDirectory\Williams,Bob 7264"

to:

strRootPath = "J:\MyDirectory\Williams"",""Bob 7264"

Let me know how it goes

Display More

Might be the cause of your problem

Have a fiddle with it and let me know how it goes!

Re: Add Refedit To Userform At Runtime

Glad it works

I didnt know to use it, I found it out the same way I find out most of the things i learn, but trying different things when im stumped(and sick of the Invalid Class String message).

I knew the refedit control wasnt part of the forms library though and the object browser got me thinking:

Quote

Class RefEdit
Member of RefEdit
Ref Edit Control

And from that just trying different ways of writing it(more dumb luck then anything)

Re: Add Refedit To Userform At Runtime

I think i may be missing something here and may come off sounding a bit silly

but could you not use this :

Private Sub UserForm_Initialize()
Dim ctlRefEdit As Control


Set ctlRefedit = UserForm1.Controls.Add("RefEdit.Ctrl")


End Sub

Re: Sumif Max Or Min

You could try the following :
=SUM(MAX(A2:C2),MAX(A3:C3), MAX(A4:C4))

Re: Macro To Fill Shape With Click

Hi

Try pasting the below code into a module:

Sub changefill()


    ActiveSheet.Shapes("Oval 1").Select 'change oval 1 to whatever your shape is called
    If Selection.ShapeRange.fill.Visible = msoFalse Then
    Selection.ShapeRange.fill.Visible = msoTrue
    Selection.ShapeRange.fill.Solid
    Selection.ShapeRange.fill.ForeColor.SchemeColor = 8
    Else
    
    Selection.ShapeRange.fill.Visible = msoFalse




    End If
End Sub

then right click on your shape and select assign macro, choose this macro from the list and away you go!

Let me know how it works for you

Re: Launching Windows Explorer Path Error

Just as an aside if you dont want to change the folder name, you may try changing :

strRootPath = "J:\MyDirectory\Williams,Bob 7264"

to:

strRootPath = "J:\MyDirectory\Williams"",""Bob 7264"

Let me know how it goes

Re: 1004 Runtime Error In "with" Statement

Not sure exactly what you are trying to do as you havent stated anything after .Range("Post") but try something like :

With Sheets("List_Data")
    .Range("Post").select 
End With

Other then that, does the worksheet and the named range you are referencing exist ?

Re: Copy & Paste Cell Value With Beforerightclick

If you will be right clicking in the "Totals" sheet, go to the "totals" sheet in the IDE and paste the code in there.

Also change

Sheets("Sheet2").Range("B2").Value = Target.Value

to

Sheets("Details").Range("B2").Value = Target.Value

Re: Copy & Paste Cell Value With Beforerightclick

Can you try something like this(entered into the relevant sheet):

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Sheets("Sheet2").Range("B2").Value = Target.Value
End Sub

Change the sheet name to suit

Let me know how it goes

Re: Don't Close Word Until Print Dialogue Screen Is Finished

Hi there!

Can you add something like the below code:

Dim printanswer As Boolean


printanswer = Application.Dialogs(xlDialogPrint).Show


If printanswer = False Then
'this means the print dialog has been closed
Else
'this means they have clicked OK
End If

This forces the code to wait for a response before continuing...give it a go and let me know how it turns out!